[northern_brewer]

I am trying to determine how many days have elapsed since an order was placed.

I am using a Formula (numeric) field to do the calculation, and setting the formula to this: {today}-{trandate}

I expected to get a nice round integer, like 3, but instead I get 3.55820601851851851851851851851851851852

I can clearly round my results down to solve the problem. Is that the cleanest way to solve the problem? I’m looking for a durable solution that would solve for daylight savings time changes, etc.
This is a cached copy. Click here to see the original post.

[k_dunc]

You can always use the “Formula (Currency)” instead of “Formula (Numeric)”. This will return the value by default to two decimal places. Alternatively, try the “ROUND()” function in your formula.

[northern_brewer]

Yes, I’ve decided to use CEIL({trandate}-{today}) in my search for now. I’m still wondering what the fractional part of the decimal number represents. I’m concerned my search could provide unexpected results at different times of day, in different time zones, before/after daylight savings time, etc.

[girieshg]

What’s the end goal here? If it’s just to see how many days elapsed on open orders, there’s a “Days Open” filter / column available by default. If you want the age of the order in days, use the function “Age in Days” on the Date field.

[Dew]

Just FYI, {today} includes a factor for the time of day (so at noon it would essentially have a “.5” at the end). You can use the TRUNC function to reduce this to a “day only” value like so: TRUNC ( {today} )

[sprintz]

Originally posted by Dew

View Post

Just FYI, {today} includes a factor for the time of day (so at noon it would essentially have a “.5” at the end). You can use the TRUNC function to reduce this to a “day only” value like so: TRUNC ( {today} )

To expand on Dew’s comment, simply modifying your original formula(numeric) result will yield the result you are after

TRUNC({today})-TRUNC({trandate})

This will strip the time portion of the dateTime object and give you nice whole numbers.

[Author]

Posts