Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

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.

1 Reply

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?