Calculated Field Rounding on Date

[ThomasNe2]

CONCAT(YEAR({DE:UKI - Campaign Start Date}), "/",CASE(MONTH({DE:UKI - Campaign Start Date}),"01","02","03","04","05","06","07","08","09","10","11","12"), "/", CASE(DAYOFMONTH({DE:UKI - Campaign Start Date}),"01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31"))

The above is some code I have in place to change the format of a custom date and time field named 'UKI - Campaign Start Date'. The Calculated Date Field is working well but seems to round in the wrong direction when it comes to dates with midnight. A date entered of '28 Aug 2025 00:00' results in a generated date of '2025/08/27'. The dates seem to round down when a time of 00:00 is added. Is there a reason the date would round in either direction based on the code I have added? 

[skyehansen]

I'm not getting the rounding down effect, when using your code. There are a couple of possibilities -- one being that you went through several iterations and forgot to click the "apply to existing calculations" checkbox. The other being that there's something about the custom date field you're not telling us about (I'm using just a regular date and time field, and actually am not able to specify 00:00 -- it reverts me to 12:00AM).

One workaround that you can try is adding a CLEARTIME function to the DAYOFMONTH to try and strip the time off.

Generally, I also suggest some troubleshooting to nail down exactly where it's happening. e.g. what happens when you just do a test calc for DAYOFMONTH({DE:yourfield}) -- if it stops rounding down but starts up again when you add a CASE to this, you'll know it's behavior specific to the CASE function. However, if you add the CASE function back in, and it still doesn't round down, you'll know there's something else going on with your expression -- so just keep adding chunks back in, one at a time. 

[ThomasNe2]

Hello Skye 

There's nothing unusual about the date field that I can see. It's just a custom field. 

When I remove the CASE function the date extracted is still rounding down. 

[skyehansen]

have you tried my cleartime suggestion? Otherwise I would suggest taking it to Workfront Support. 

[kautuk_sahni]

@ThomasNe2 Just checking in! Were you able to get this resolved? If you found your own solution, sharing the details would be a big help to others who might face the same issue later on. And if one of the replies here helped, whether it fully solved the problem or simply pointed you in the right direction, marking it as accepted makes it much easier for future readers to find. Thanks again for helping close the loop and contributing to the community!

[kautuk_sahni]

@ThomasNe2 Just checking in! Were you able to get this resolved? If you found your own solution, sharing the details would be a big help to others who might face the same issue later on. And if one of the replies here helped, whether it fully solved the problem or simply pointed you in the right direction, marking it as accepted makes it much easier for future readers to find. Thanks again for helping close the loop and contributing to the community!