Issue with Month and Day functions treating a date as 1 day old

Question

I have 3 columns - a date column and two calculated columns.
DOB - date/time with date only format
DOB_Month - calculated column Month(DOB) - format as number zero decimals
DOB_Day - calculated column Day(DOB) - format as number zero decimals

On some list entries, the calculations work fine. On some - the functions seem to be treating the date as being one day back. (ie: I enter 12/1/2022, the calculations are treating the date to be 11/30/2022. See graphic. If I edit a value in the list (such as Name) and save, the calculations correct themselves... only to revert to wrong values in 3 minutes upon internal refresh. All the records in this screenshot were entered at the same time.

Can someone explain why SharePoint is sooooo erratic and random on these types of issues/applications. I have a very difficult time trying to promote this platform, when the basics of logic don't work consistently.

Answer 1

Hi @Gregg Jenkins ,
Great to know that and thanks for sharing the update here.

By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others.". and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

[Issue with Month and Day functions treating a date as 1 day old]

Issue Symptom:
I have 3 columns - a date column and two calculated columns.
DOB - date/time with date only format
DOB_Month - calculated column Month(DOB) - format as number zero decimals
DOB_Day - calculated column Day(DOB) - format as number zero decimals

On some list entries, the calculations work fine. On some - the functions seem to be treating the date as being one day back. (ie: I enter 12/1/2022, the calculations are treating the date to be 11/30/2022. See graphic. If I edit a value in the list (such as Name) and save, the calculations correct themselves... only to revert to wrong values in 3 minutes upon internal refresh. All the records in this screenshot were entered at the same time.

Current status:
Short Answer: Regional Settings were set to Pacific time. I sit in Eastern time. Once reconfigured, the issue seems to be resolved.

Long Answer:

Yanli -

These are exactly the formulas I'm using.

It appears the issue is associated with regional settings. My site was based off the MSFT HybridWork template design. That template is configured with the regional setting of Pacific time, but we are located in Eastern time. I recreated my list in a team site, with proper settings and had no issues with date inputs. Upon comparing the sites, I noticed the regional setting. I have reconfigured my communications site with the proper setting and, so far, all seems to be working as expected.

I can't quite explain why having the regional setting set to Pacific time was the issue. Two entries I made were 10 seconds apart: one was fine, the other incorrect. They were entered mid-morning, so even if somehow the system thought my date value should be treated 3 hours back using the created TS, it would've computed to 7am, not preceding day. And if it assumed my date field (Date only format) was treated as 12am, and then adjusted for timezone shift (3 hours back) then ALL dates would've been affected.

So - I can't explain why this was happening, and why reconfiguring the timezone fixed the issue --- but it seems to have fixed it.

You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community member's to see the useful information when reading this thread. Thanks for your understanding!

Answer 2

Hi @Gregg Jenkins ,
According to my research and testing, your problem cannot be reproduced, which may be related to the formula you use.
I used the following formula to test and it worked fine.



You can try to see if the problem is solved.
*
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Short Answer: Regional Settings were set to Pacific time. I sit in Eastern time. Once reconfigured, the issue seems to be resolved.

Long Answer:

Yanli -

These are exactly the formulas I'm using.

It appears the issue is associated with regional settings. My site was based off the MSFT HybridWork template design. That template is configured with the regional setting of Pacific time, but we are located in Eastern time. I recreated my list in a team site, with proper settings and had no issues with date inputs. Upon comparing the sites, I noticed the regional setting. I have reconfigured my communications site with the proper setting and, so far, all seems to be working as expected.

I can't quite explain why having the regional setting set to Pacific time was the issue. Two entries I made were 10 seconds apart: one was fine, the other incorrect. They were entered mid-morning, so even if somehow the system thought my date value should be treated 3 hours back using the created TS, it would've computed to 7am, not preceding day. And if it assumed my date field (Date only format) was treated as 12am, and then adjusted for timezone shift (3 hours back) then ALL dates would've been affected.

So - I can't explain why this was happening, and why reconfiguring the timezone fixed the issue --- but it seems to have fixed it.