Why is the DATEONLY formula outputting future dates?

Hector Mendez

Any time stamp after 7pm will be counted for the following date. Please see snapshot below. The time stamp is auto generated when the user fills out a form.

Paul Newcome

It is because of timezones. You are going to need a formula that grabs the hour, searches for "PM" and then subtracts 1 for anything greater than 7pm.

It will look something like this...

=DATEONLY(Created@row) - IF(AND(FIND("PM", Created@row) > 0, VALUE(SUBSTITUTE(MID(Created@row, 10, 2), ":", "")) > 7), 1, 0)

Paul Newcome

It is because of timezones. You are going to need a formula that grabs the hour, searches for "PM" and then subtracts 1 for anything greater than 7pm.

It will look something like this...

=DATEONLY(Created@row) - IF(AND(FIND("PM", Created@row) > 0, VALUE(SUBSTITUTE(MID(Created@row, 10, 2), ":", "")) > 7), 1, 0)

Mila

for me it shows the next date anytime after 3pm. So i modified the formula accordingly and it works, BUT… the timestamps from early afternoon hours, such as for example 12:30 pm is now showing up as previous day's date.

edit: the problem is when the timestamp is 12:xx pm. because 12 > 7.

i fixed it by adding another condition in the AND() expression: VALUE(SUBSTITUTE(MID(Created@row, 10, 2), ":", "")) <>12

Paul Newcome

@Mila Here is a link to quite a few time based solutions including an updated time zone conversion:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p1

.