Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Replies
Total Likes
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!