Help with Date calculation | Community
Skip to main content
T
TiffanySm1
January 6, 2021
Question

Help with Date calculation

  • January 6, 2021
  • 1 reply
  • 818 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

imgrund
Adobe Employee
imgrundAdobe Employee
Adobe Employee
January 6, 2021

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),""))))

    T
    TiffanySm1Author
    January 6, 2021

    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)...

    B
    BradCr
    Level 2
    February 19, 2021

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

    Terms & ConditionsAccessibility statement

    Loading footer...