Subtracting Dates - Smartsheet is rounding date/time up

Diana Chamberlain

I am trying to subtract two dates to get the duration of time something stayed within a phase. Currently we are trying to use "CreatedDate," the auto-generated field as the "start" and another date field as the "finish", let's call it "Date entered Triage".

The formula [Date Entered Triage] - [Created Date] works great for any instance where [Created Date] is before 5pm. Anything after 5pm looks like it's getting rounded up in the backend to the next day.

To get around this, I have tried using LEFT() to parse out the date from the time, but this is not working. The [Date Entered Triage] - [Created Date] formula then returns an invalid operation - it seems date fields can't be used in a formula if the date itself is derived from a formula.

Has anyone else experienced something similar and found a solution? I feel like this one should be elementary math, but unfortunately is being made more complicated than it needs to be...

Thanks!

Paul Newcome

Pulling data using a LEFT, RIGHT, MID, or JOIN function converts that data to a text string. To be able to use a date in a formula, you need to have it formatted as a date either in a DATE function or from a date type of column.

Since you are using a system generated date/time stamp column, you can actually use a DATEONLY function to pull just the date from the data.

=[Date Entered Triage]@row - DATEONLY([Created Date]@row)

Diana Chamberlain

Thank you Paul, you are amazing!! Do you know if there is a way to not have it "round up" if the time stamp is past 5pm, without having to change my project working-day settings? For example, if the CreatedDate date/time stamp is 03/06/20 5:29 PM, the "DATEONLY" function is returning 03/07/20

Paul Newcome

Yes. We would need to isolate the hour and convert it to a number then combine that along with a search for a "p" in an IF statement to say that if the number is 5 or greater and there is a "p", then subtract 1 from the number of days.

Can you provide a screenshot of your timestamp column?

Diana Chamberlain

Paul Newcome

Ok. So the first digit of your hour is going to be 10. The second digit (if there is one) is going to be one less digit than the colon.

So first we isolate the hour and convert it to a number:

=VALUE(MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10))

We also need to determine if there is a "p" in your time column.

=CONTAINS("P", [Created Date]@row)

Next we say that IF the hour is greater than or equal to 5 AND there is a "p", subtract 1.

=IF(AND(VALUE(MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10)) >= 5, CONTAINS("P", [Created Date]@row)), -1)

Then we add this to the already established formula like so...

=([Date Entered Triage]@row - DATEONLY([Created Date]@row)) + IF(AND(VALUE(MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10)) >= 5, CONTAINS("P", [Created Date]@row)), -1)

kirstie858

For some reason this formula didn't work for me:

=IF(AND(VALUE(MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10)) >= 5, CONTAINS("P", [Created Date]@row)), -1)

I tweaked it a little, and this formula gives me the date portion from a datetime column:

=IF(AND(CONTAINS("P", [Created Date]@row + "") = true, MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10) >= 5), DATEONLY([Created Date]@row - 1), [Created Date]@row)