Calculated Field Rounding on Date | Community
Skip to main content
T
ThomasNe2
New Participant
September 4, 2025
Question

Calculated Field Rounding on Date

  • September 4, 2025
  • 1 reply
  • 306 views
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? 

1 reply

skyehansen
skyehansen
New Participant
September 4, 2025

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. 

T
ThomasNe2Author
New Participant
September 5, 2025

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
skyehansen
New Participant
September 5, 2025

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

Terms & ConditionsAccessibility statement

Loading footer...