+ Start a Discussion
Jennifer WilsonJennifer Wilson 

Day of Week showing incorrectly in Report

Hello

I have a formula on a custom object to show the day of the week:

CASE(MOD( Datevalue(CKSW_BASE__Start__c) - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,"Friday","")

For records post clock change on 29 March 2021, the day of the week is showing incorrectly if the start time of the record is 00:00.

Example:

28/03/2021 18:30  29/03/2021 06:30  Sunday
28/03/2021 21:00  29/03/2021 07:00  Sunday
28/03/2021 22:00  29/03/2021 08:00  Sunday
29/03/2021 00:00  29/03/2021 23:59  Sunday
29/03/2021 00:00  29/03/2021 23:59  Sunday
29/03/2021 00:00  29/03/2021 23:59  Sunday

Can anyone provide any help with this please?
Best Answer chosen by Jennifer Wilson
AnudeepAnudeep (Salesforce Developers) 
Hi Jennifer, 

You are seeing this difference because you are using DATEVALUE()

As per the documentation

The DATEVALUE() formula option provides more accurate daylight savings time values without workarounds. The option avoids an existing one-hour discrepancy when processing times between 11:00 PM and 1:00 AM.

Try using alternatives to Datevalue() and you should not see any difference. As far as I know, this is working as designed

Let me know if this helps, if it does, please mark this answer as best so that others facing the same issue will find this information useful. Thank you

All Answers

AnudeepAnudeep (Salesforce Developers) 
Hi Jennifer, 

You are seeing this difference because you are using DATEVALUE()

As per the documentation

The DATEVALUE() formula option provides more accurate daylight savings time values without workarounds. The option avoids an existing one-hour discrepancy when processing times between 11:00 PM and 1:00 AM.

Try using alternatives to Datevalue() and you should not see any difference. As far as I know, this is working as designed

Let me know if this helps, if it does, please mark this answer as best so that others facing the same issue will find this information useful. Thank you
This was selected as the best answer
Jennifer WilsonJennifer Wilson
Thank you Anudeep!