Why is 'DAY' Value from a Date being populated incorrectly?

jmarkovi

I have a 'Formatted Date' column with a column formula (below) that formats the date from the row's date timestamp, which I renamed to 'Training Completion Date', into DD-MMM-YYYY format. All rows but one is getting the correct day from the DATE. I cannot figure out why 10/08/24 returns 9-Oct-2024. Curious to know if anyone else has had this issue and what a potential resolution is.

Formula is:
=DAY(([Training Completion Date]@row)) + "-" + IF(MONTH([Training Completion Date]@row) = 1, "Jan") + IF(MONTH([Training Completion Date]@row) = 2, "Feb") + IF(MONTH([Training Completion Date]@row) = 3, "Mar") + IF(MONTH([Training Completion Date]@row) = 4, "Apr") + IF(MONTH([Training Completion Date]@row) = 5, "May") + IF(MONTH([Training Completion Date]@row) = 6, "Jun") + IF(MONTH([Training Completion Date]@row) = 7, "Jul") + IF(MONTH([Training Completion Date]@row) = 8, "Aug") + IF(MONTH([Training Completion Date]@row) = 9, "Sep") + IF(MONTH([Training Completion Date]@row) = 10, "Oct") + IF(MONTH([Training Completion Date]@row) = 11, "Nov") + IF(MONTH([Training Completion Date]@row) = 12, "Dec") + "-" + YEAR([Training Completion Date]@row)

Edit: 5:11 PST: Out of curiosity, I submitted a test entry in my form after 5PM to see what would happen. The DAY returned tomorrow, 10, instead of today. Why is this happening and how can I return the actual DAY.

Thank you.

-Joel

SSFeatures

Hi @jmarkovi,

The problem is that SmartSheet stores the dates using the GMT timezone (Greenwich Mean Time) rather than storing them using your local timezone. So for example, if your timezone is PST then it is 7 hours behind GMT. If your timezone is PST and Training Completion Date shows 5:14 PM, then behind-the-scenes SmartSheet is storing it as 5:14 PM + 7 hours which is 12:14 AM on the next day. This explains why using DAY gives you the next date.

If you and all of your team are using the same timezone, a quick solution is to just subtract a certain number of hours from the time, and then using that adjusted number in your DAY function. If you are in PST then you can just subtract 7 hours.

Here is another useful thread:

I have a problem with getting a formula to deal with the difference in GMT and NZDT

Heather C

Nov 5, 2023

I have tried to add a date only column =DATEONLY(Created@row) that feeds off the Created column, but am finding that any entry prior to 1pm is showing the previous day's date. We are GMT + 13 at the moment in New Zealand as it is Daylight Savings but would normally be GMT + 12.

https://us.v-cdn.net/6031209/uploads/MCCGYSD19ZGV/date-problem.png

I am not that good with formulas so any help would be appreciated, thank you.

---

View Post

Best!

SSFeatures

Hi @jmarkovi,

The problem is that SmartSheet stores the dates using the GMT timezone (Greenwich Mean Time) rather than storing them using your local timezone. So for example, if your timezone is PST then it is 7 hours behind GMT. If your timezone is PST and Training Completion Date shows 5:14 PM, then behind-the-scenes SmartSheet is storing it as 5:14 PM + 7 hours which is 12:14 AM on the next day. This explains why using DAY gives you the next date.

If you and all of your team are using the same timezone, a quick solution is to just subtract a certain number of hours from the time, and then using that adjusted number in your DAY function. If you are in PST then you can just subtract 7 hours.

Here is another useful thread:

I have a problem with getting a formula to deal with the difference in GMT and NZDT

Heather C

Nov 5, 2023

I have tried to add a date only column =DATEONLY(Created@row) that feeds off the Created column, but am finding that any entry prior to 1pm is showing the previous day's date. We are GMT + 13 at the moment in New Zealand as it is Daylight Savings but would normally be GMT + 12.

https://us.v-cdn.net/6031209/uploads/MCCGYSD19ZGV/date-problem.png

I am not that good with formulas so any help would be appreciated, thank you.

---

View Post

Best!

jmarkovi

Thank you!! I was able to use: =VALUE(MID(Created@row + "", 4, 2)), replacing 'Created' with the my revised column name to get the correct day. Appreciate this help!!

SSFeatures

You're welcome, I'm glad that the fix was super smooth!