Dateonly rounding issue

I'm trying to calculate networkdays from system generated date created. I used the dateonly to pull the date out of the system date created but it seems to be rounding up at some designated time of the day which causes all concurrent formulas to be off

Find more posts tagged with

Comments

KrisWalsh

I have no idea if these suggestions will work, but this is what I would test.

Create a new colum "Modified Date Only" and base your formula on it, instead of the system column.

Row1 Formula: =$Modified1

If that doesn't work

Row1 Formula: =MIN($Modified1)

***Absolute Column Reference $ is just a best practice***

SashaR

KrisWalsh suggestion should work, just pull the date over to a date column then run the formulas off the new column.

If you are still having trouble, could you show us an example of the issue?

Nick N

Well I understand the idea but whether I use the formula above or dateonly formula at a certain created time of day the date rounds up causing the calculations for running days to be off. EX: Create column shows 2/24/2016 10:16pm. Date only shows 2/25/2016

J. Craig Williams

Nick,

I may know what is going on.

System date columns have a date and a time. If one looks at the column type it will be "Date/Time" not "Date" like non-system date columns.

If you strip out the date to only show the time, like this:

=[Due Date]23 - DATEONLY([Due Date]23)

for the Due Date column in row 23, you'll get a number.

That number multiplied by 24 will tell you what hour the system thinks the date is - start or finish.

If you have an 8 hour work day, the start time for the work day will be 8am.

Here's one of the tricky parts - the end time for an 8 hour work day will be 5pm.

The system thinks there is an hour lunch period from 12pm to 1pm.

And that hour lunch is at noon regardless of whether the day is 8 hours or more.

Here's the next tricky part and likely what is causing the problem - after 5pm, it is "tomrrow" according to Smartsheet.

Once you pull the date out of the system date columns, depending on when in the day the system thinks the time is will determine what you see when checking for elapsed duration.

Depending on what you really want to do with the times before 8am or after 5pm (assuming an 8 hour day), you might try this:

=IF(OR(Created3 - DATEONLY(Created3) < 0.33333, Created3 - DATEONLY(Created3) > 0.70833), DATEONLY(Created3) - 1, DATEONLY(Created3))

0.3333 is 8am. 0.70833 is 5pm

If it is before 8am or after 5pm, subtract 1 day, otherwise, use the day part.

I'll need to wait until tomorrow to verify that I have the before 8am part correct.

Hope that helps.

Craig

KrisWalsh

JCW = Genius Rocket Surgeon...

I'm just a simple caveman... This worked in my sheet. Would it break given what you've described?

=MONTH($Modified1) + "/" + DAY($Modified1) + "/" + YEAR($Modified1)

Example Sheet

Just realized: I happen to be on the west coast.

It's 02/24/16 10:42pm right now.

My SS Personal Settings are set to home. (Austin/Central Time). When I put in this formula it entered 02/25/16, because the Modified Date is 02/25/16 12:42am.

Begs the questions,

1. Nick, are people entering data from a bunch of different time zones?

2. JCW, if so, doesn't this add a 'smidge' of complexity to your revelations?

*** Now my head hurts... Thanks! ***

J. Craig Williams

KrisWalsh,

Might be too soon to declare victory.

I am getting odd results (ie wrong) and while there is a glimmer of a pattern, there ... isn't one I've found yet.

I have values for (Date - DateOnly) that are greater than 1.

Sometimes, takes the DateOnly - 1 results in the right answer, sometimes not.

VERY confusing.

I'm currently only experimenting with the two system columns Created (Date) and Modified (Date). Next I'll try the two Date fields (when Dependencies are set).

Here's my editable test sheet.

The red highlight is Date - DateOnly > 0.08 (shortly after 5pm using my old knowledge)

https://app.smartsheet.com/b/publish?EQBCT=b96db72790b6411a854fb342631513e8

I'm traveling today and likely won't be able to look at this again until tomorrow.

Here's two values and date times.

0.9947502/18/16 06:52 PM

0.1747102/19/16 11:11 PM

To time stamp must have a different algorithm than the dependency enabled dates.

Craig

J. Craig Williams

More info:

A Date column will still "remember" the time but difficult to access it.

=Created23

will show 02/26/16 for example

so adding (1 - Created23 - Dateonly(Created23))

is enough to get the date to be tomorrow. (0.99 - .... is not)

That is, if the Created time stamp is 11pm, it only takes a little to kick over to the next day.

Also determined that any task beginning at the start of a work day will start at 8am for all work-days less than or equal to 12 hours.

Work-days longer than 12 hours start at midnight.

Since my normal working hours values of Created-DateOnly(Created) don't match and changing my working hours to 24 from 8 will change my calcs, I need to rethink my approach.

Or wait long enough for a Smartsheet developer to tell us what the answer is.

My head hurts too now.

Going to think about something else for a while.

Craig

Tamara K

Sounds like this mystery was never solved...did anyone have any new epiphanies?

I'm trying to create a flag that shows anytime the manually-entered date does not match the System Created date column. I tried creating a new column and pulling the "DATEONLY" from the system Created column and comparing that to the manually-entered date, but, as you all discovered, if it was entered - say - around 11pm, it wants to roll over to the next day.

J. Craig Williams

The "rounding" is because of the way Smartsheet handles UTC in the system cell.

You are comparing a manually entered date to the Created date, not the Modified date?

Would it be permissable to use the Created date directly (via a formula), if one could be provided that would get around the UTC problem?

That is, match the displayed date (as a date), not the value date.

Craig

tfountain47446

Hi all,

I ran into this funny business as well today as I wanted to simply calculate the number of days between an auto-generated "Create Date" column and a User entered Requested Finish Date. I wanted to ignore the Time of Day that the Request was created (this is a simple Service Request tracker). After seeing a problem and suspecting a round up issue I found my way to this forum which confirmed the issue.

Ultimately it hit me what I really wanted was the date portion of the auto-gen Column and since DateOnly() couldn't do it I simply parsed out the Year, Month, and Day using MID (easy since consistent structure) and fed it to a DATE() function. I happened to store that in a Date column then happily could use that calculated Date in other formulas (like one where I assign Red Yellow or Green status balls to the task. Formula below where [Request Date] is the auto-gen column. Note you need VALUE() to make the extracts into numbers not text. Appears to work fine. thanks for the insight of prior posts...

=DATE(2000 + VALUE(MID([Request Date]1, 7, 2)), VALUE(MID([Request Date]1, 1, 2)), VALUE(MID([Request Date]1, 4, 2)))