Expand my Community achievements bar.

Help with Date calculation

Avatar

Level 1

I'm trying to set up a calculated field on a request form that outputs a specific date based on a Fiscal Quarter value selected in another field. I have a drop-down field on a request form called P - Estimated Quarter to Start where the requester can choose Q1, Q2, Q3, or Q4. In my calculation, I want to determine an actual date for the quarter value chosen. For example, if the requester chooses Q1, I want my calculated field to return a date of February 1, 2021.

Here is the calculated expression I wrote, but it doesn't seem to be working:

IF(CONTAINS("Q1",{DE:P - Estimated Quarter to Start}),DATE(3/12021),IF(CONTAINS("Q2",{DE:P - Estimated Quarter to Start}),DATE(5/1/2021),IF(CONTAINS("Q3",{P - Estimated Quarter to Start}),DATE(8/1/2021),IF(CONTAINS("Q4",{DE:P - Estimated Quarter to Start}),DATE(11/1/2021)))))

Any ideas for what I'm doing wrong? Am I using the wrong combination of expressions? Do I have a syntax error?

Thanks in advance for the help!

Tiffany

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

3 Replies

Avatar

Level 10

Hi - You have some inconsistencies. For instance, you are missing a / in March 1 2021 (which I think you wanted February right?) and the DE: for Q3 before the field.

Now for me, I would have done it like this...

IF({DE:P - Estimated Quarter to Start}="Q1",DATE(2/1/2021),IF({DE:P - Estimated Quarter to Start}="Q2",DATE(5/1/2021),IF({DE:P - Estimated Quarter to Start}="Q3",DATE(8/1/2021),IF({DE:P - Estimated Quarter to Start}="Q4",DATE(11/1/2021),""))))

Avatar

Level 1

Good catch on my syntax errors! I also liked the way you set up your expression better, but when I tried it on the form, the calculation is giving me a date with the year 2070. I wonder if I need to write the string for my DATE() expression differently (e.g. spell it out like February 1, 2021)...

Avatar

Level 2

I'm having the same issue (getting a 1970 date)! Did you find any resolution to this?