Date modified reformatting irregularity

Topic Labels: Formulas
468 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi all, I have set up a formula to reformat a field that contains adjusted “date modified” info but the time (minutes) part of the date-time modified like field are different.
Eg I have one field for Date modified, then another field that uses the formula “DATEADD({Date Modified UTC+12/GMT},3,‘hours’)” to add 3 hours to the date modified.
Then another field using the formula “DATETIME_FORMAT({Date Modified +3 HOURS}, ‘YYYY-MM-DD HH:MM:SS’)” to get the adjusted date modified data into the right format.
The adding 3 hours part is working fine, however the time is changing slightly at the date reformat stage.
Eg field A has date modified showing as “2021-03-20 6:26pm”
Field B is successfully adding 3 hours and showing “3/20/21 9:26pm”
But Field C is changing the time slightly when reformatting and showing “2021-03-20 21:03:00” (rather than 21:26:00 for HH:MM:SS).
Interestingly, when I add a comment to a record to change the date modified and test, although the first 2 fields are updating fine, every record I have tested is showing the same time (“21:03:00”) for Field C time even when the modified date/time is different for each record and showing different modified date/times in the other fields. Anyone got any ideas?Screen Shot 2021-03-21 at 7.35.55 AM

1 Reply 1

Welcome to the community, @Patrick_Fallon! :grinning_face_with_big_eyes:

This is where your problem lies. Notice that you’re using “MM” for both months and minutes. The minutes specifier should be lowercase (“mm”, not “MM”). Also be aware that “SS” gives you fractional seconds, where “ss” gives you whole seconds.

For future reference, it might be good to bookmark this page: