Using A Static Date In Calculated Field

[JonathanTh]

Hi I have a question around using a static date in a calculated date field as part of a string of IF statements. Basically I want Workfront to mark something as N/A (false expression) if the entry date of an issue was before a given date.. I.E. IF(Entry Date>"22/04/19",True Expression,False Expression) I assume it is something to do with the date format, but I cannot figure out how to make this work. Thanks Jonathan Thain

[JonathanTh]

Replying to my own post here, but I *think* I have it working by using the following format.: IF(Entry Date>"2019-04-22",True Expression,False Expression) Not sure if anyone can confirm this? Thanks Jonathan Thain BT Group

[Doug_Den_Hoed__AtAppStore]

Hi Jonathan, Yep: confirmed. IF(Entry Date>"2019-04-22",True Expression,False Expression) worked as expected, as did IF(Entry Date<"2019-04-22",True Expression,False Expression), provided I recalculated custom data expressions after the formula change, which is As Designed. Regards Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[JonathanTh]

Just revisiting this.

For some reason this seems to have stopped working, and using a static date is returning the false expression every time?

I had originally set out to create another variant of this, but noticed when I looked at the previous one from a few months ago (after having no joy with the new one) it also now fails to return a true expression?

Is anyone able to confirm if anything has changed that would prevent this from working now?

Thanks Jonathan Thain BT Group

[acorya]

I found this thread while looking for an answer to how I need to format a static date in a calculated field. Short answer is DATE ("YYYY-MM-DD") e.g. DATE("2023-03-23") or DATE("MM/DD/YYYY") e.g. DATE("03/23/2023").

For my use case I needed an IF statement that would return a text string if a custom date field is between two static dates. What I ended up with is this:

IF({DE:Outcome date changed}>DATE("2023-05-28")&&{DE:Outcome date changed}<DATE("2022-06-25"),"FY23 PD01",
IF({DE:Outcome date changed}>DATE("2022-06-25")&&{DE:Outcome date changed}<DATE("2022-07-23"),"FY23 PD02",
IF({DE:Outcome date changed}>DATE("2022-07-23")&&{DE:Outcome date changed}<DATE("2022-08-20"),"FY23 PD03",
IF({DE:Outcome date changed}>DATE("2022-08-20")&&{DE:Outcome date changed}<DATE("2022-09-17"),"FY23 PD04",
"Out of date range"
))))

I used double ampersand && as an AND statement to require both conditions to be met for it to be true. As time goes on I can add rows to capture upcoming date ranges according to the company's accounting period schedule.

Hope this helps someone else!