Troubleshooting formulas

Work dates and dates don't match

Use the ToDate() function to convert a work date to a date

Find the duration between two dates

Field type:
Formula - Duration

Formula in error:
[Actual Finish]-[Actual Start]

Why it doesn't work:
[Actual Finish] is a Date field and [Actual Start] is a Work Date field

Solution:
Convert [Actual Start] to a date value

Revised formula:
[Actual Finish]-ToDate([Actual Start])

Find the duration between two date/time values

Field type:
Formula - Numeric

Formula in error:
WeekdaySub([Date Modified],[Date Created])

Why it doesn't work:
[Date Modified] and [Date Created] are Date / Time type fields

Solution:
Convert Date/Time fields to Date fields

Revised formula:
WeekdaySub(ToDate([Date Modified]),ToDate([Date Created]))

Generate a date that's two weekdays after the event in a date / Time field

Field type:
Formula - Date

Formula in error:
Weekdayadd ([date of meeting], 2)

Why it doesn't work:
[Date of meeting] is a Date / Time type field

Solution:
Convert the result to a date

Revised formula:
ToDate(Weekdayadd ([date of meeting], 2))

Numbers don't mix with dates and durations

Convert the number to a duration using a function like Days()

Calculate how many gallons are used per hour

Note: You must understand what units the numeric value has and then select the corresponding conversion formula - ie hours vs days.

Field type:
Formula - Numeric

Formula in error:
[gallons consumed] / [hours of operation]

Why it doesn't work:
[Gallons consumed] is a Numeric type field and [hours of operation] is a Duration type field

Solution:
Convert the duration into a number

Revised formula:
[gallons consumed]/ToHours
([hours of operation])

Calculate the difference between the days estimated to complete a job versus the actual days it took

Field type:
Formula - Numeric

Formula in error:
[Estimated Duration] - [Actual Duration]

Why it doesn't work:
[Estimated Duration] is a numeric field. [Actual Duration] is a Formula - Duration type field

Solution:
Use the ToDays function to convert the duration into a numeric value that shows the number of days

Revised formula:
[Estimated Duration] - ToDays([Actual Duration])

Find the number of months between two dates

Field type:
Formula - Numeric

Formula in error:
[end date] - [start date])/30

Why it doesn't work:
[end date] and [start date] are both Date fields

Solution:
Convert the number 30 to days

Revised formula:
(([End Date] - [Start Date]) / Days(30))

Calculate depreciation based on time passed

Field type:
Formula - Numeric

Formula in error:
[elapsed months]*[depreciation this period]

Why it doesn't work:
[elapsed months] is a Formula -Duration type field and [depreciation] is a Numeric field.

Solution:
Convert the duration value so you can multiply by it.

Revised formula:
ToNumber([elapsed months])*[depreciation this period]

Calculate a task's finish date, based on the start date and number entered in the [Estimated # of Days] field.

Field type:
Formula - Date

Formula in error:
[Actual Start Date]+ [Estimated # of days]

Why it doesn't work:
[Estimated # of days] field is a Numeric field.

Solution:
Use the Days function to convert the number to a duration value of days

Revised formula:
[Actual Start Date]+ Days([Estimated # of days])

Literals often need to be converted to a data type (What's a literal?)

Use a conversion function to change the result to a different data type.

Use Ebenezer red to color projects assigned to a user

Field type:

Formula - Checkbox (in Report Builder)

Formula in error:
If([Project Lead]="Ebenezer", "pink", "")

Why it doesn't work:
[Project Lead] is a User type field

Solution:
Use the ToUser() function to send an email.

Revised formulas:

Send by email:
If([Project Lead]=ToUser("Ebenezer@ScroogeandMarley.biz"), "red", "")

Or send by user name:
If([Project Lead]=ToUser("EScrooge"), "red", "")

Mark records where start time is after 6 am

Field type:
Formula - Checkbox

Formula in error:
If([Start Time]>"6:00 am", true, false)

Why it doesn't work:
[Start Time] is a Time of Day type field

Solution:
Use a conversion function to specify what this text represents

Revised formula:
If([Start Time]>ToTimeOfDay("6:00 am"), true, false)

Calculate the number of checkboxes turned on

Field type:
Formula - Numeric:

Formula in error:
[Checkbox1] + [Checkbox2] + [Checkbox3]....

Why it doesn't work:
Checkboxes cannot be added like numbers even though they represent Boolean values (1 or 0)

Solution:
Use the ToNumber() formula to return 1 for true or yes and a zero for false or no

Revised formula:
ToNumber([Checkbox1]) + ToNumber([Checkbox2]) + ...etc.

Formula draws a blank

If a formula saves but appears blank in the report, another field called by the formula might be the issue. Check these things:

• Are all fields referenced by the formula part of your app?

• Are formula fields working correctly?

• Does a referenced field include unusual values?
  e.g., if a formula calculates the result of one field divided by another , an error occurs if the divisor is 0.

A formula that includes a null function doesn't work

Go to the field's properties, select the table and field, and clear Treat blank values as "0" in calculations