DAY Function not working as intended?

tdotarcy

I have a column formula for the "Month" "Day" and "Week" from the "Auto - Date Added" Column

The column formula for DAY is

=DAY([Auto - Date Added]@row)

As you can see, the day is still the same - 7/14/22 but the day function goes to the next day (15th) after 5PM. Is there anyway to fix this / change the day function so that is interprets the day to be started at 12AM and not 5PM?

Would be much appreciated..

Thanks.

Matt Johnson

Hi @tdotarcy

You can change the hours in a day, in the settings, by following the steps below:

Switch your sheet to the Gantt View.

Click the settings wheel on the upper right, below the share button.

On the Dependency Settings screen, click the "edit" button in the Working Days section.

From there you can change the Length of Day (hours).

I hope that helps. Have a good day.

Terry Lewis

@tdotarcy - You must be in the Eastern time zone.

Smartsheet auto-dates are recorded as UTC (but displayed in your local time). At 5 p.m. Eastern Daylight UTC becomes the next day. So the DAY command is working correctly, just not off of what you are looking at. The last day of the month it will show as the next month for these seven hours.

I've overcome this by creating a helper column (formatted as a date column) with a column formula that is simply picking up the data from that auto column:

=[Auto - Date Added]@row

The new column (your column) will record the visible date from the auto column and the commands you're using when applied to your new column will return the results you expect.

I've not had occasion to test this, but you might accomplish this using a "record a date" automation where any new row just get's the new column populated. Haven't tested this theory.

Hope that helps!

Terry