Skip to main content
Solved

Correct formula to calculate a date based on dropdown selected in another field

D
Forum|alt.badge.img+4

Hi, struggling with a formula / best approach for calculating dates based on a condition.

Fields in scope are as follows:
Meeting Type = Single select option
Date = Date Meeting to take place
Request for information = calculated date based on meeting type selected.

So if for meeting type I choose ‘x’ I want request for information to populate a date 8 days prior to the ‘date’ field (which is already populated with a date)
If for meeting type I choose ‘y’ I want request for information to populate a date 11 days prior to the ‘date’ field within the request for information field.

Previously the calculated dates were identical regardless of ‘meeting type’ and the ‘DATEADD’ formula worked fine. Now I need to make it conditional, I cannot seem to get an if statement formula working correctly.

Any help would be greatly appreciated.

Best answer by TheTimeSavingCo

David_Bevan wrote:

Apologies for the further query Adam but the fix here has had a knock on effect whereby the value showing in the field now is not recognised as a date. I have views set up so that records fall into different views once one of these dates are hit (this subsequently triggers messages out to the business).

I have this set up as a filter where ‘initial request’ is on or before ‘today’ but I am now unable to use this filter as it isn’t recognised as a date. Any ideas on a workaround?


Hi David, ah, could you try this instead?

IF(
  AND({Meeting Type}, Date),
  SWITCH(
    {Meeting Type},
    "Ex-Co", DATEADD(Date, -8, 'days'),
    "Board", DATEADD(Date, -11, 'days')
  )
)

It isn’t recognized as a date anymore due to the DATETIME_FORMAT() function, and so I removed that. You should be able to filter by date now

I’ve also changed it to a SWITCH() as it’s easier to read I think

View original
Did this topic help you find an answer to your question?

6 replies

TheTimeSavingCo
Forum|alt.badge.img+28

Hi David, try this:

IF(
  AND({Meeting Type}, Date),
  IF(
    {Meeting Type} = "X",
    DATETIME_FORMAT(DATEADD(Date, -8, 'days'), "DD MMMM YYYY"),
    DATETIME_FORMAT(DATEADD(Date, -11, 'days'), "DD MMMM YYYY")
  ),
  "Select a meeting type"
)

And it’s setup here if you’d like to see it in action

Adam, Consultant @ thetimesaving.company
D
1 person likes this
  • D
    David_Bevan
D
Forum|alt.badge.img+4
  • David_Bevan
  • Author
  • Participating Frequently
  • 7 replies
  • June 23, 2022
TheTimeSavingCo wrote:

Hi David, try this:

IF(
  AND({Meeting Type}, Date),
  IF(
    {Meeting Type} = "X",
    DATETIME_FORMAT(DATEADD(Date, -8, 'days'), "DD MMMM YYYY"),
    DATETIME_FORMAT(DATEADD(Date, -11, 'days'), "DD MMMM YYYY")
  ),
  "Select a meeting type"
)

And it’s setup here if you’d like to see it in action


Hi Adam, thank you for your help with this, greatly appreciated. I have used the suggested above but for some reason, the date is staying static regardless of which meeting type I select. I have included a screenshot from the base with the new formula showing and the pertinent fields, can you see what I am doing wrong as works fine on your example base.

D
Forum|alt.badge.img+4
  • David_Bevan
  • Author
  • Participating Frequently
  • 7 replies
  • June 23, 2022
David_Bevan wrote:

Hi Adam, thank you for your help with this, greatly appreciated. I have used the suggested above but for some reason, the date is staying static regardless of which meeting type I select. I have included a screenshot from the base with the new formula showing and the pertinent fields, can you see what I am doing wrong as works fine on your example base.


Hi Adam, had a little play with the formula and renamed one of the meeting types that it wasn’t working for and it appears to be working now. Thank you so much for your assistance here.

D
Forum|alt.badge.img+4
  • David_Bevan
  • Author
  • Participating Frequently
  • 7 replies
  • June 23, 2022
David_Bevan wrote:

Hi Adam, had a little play with the formula and renamed one of the meeting types that it wasn’t working for and it appears to be working now. Thank you so much for your assistance here.


Apologies for the further query Adam but the fix here has had a knock on effect whereby the value showing in the field now is not recognised as a date. I have views set up so that records fall into different views once one of these dates are hit (this subsequently triggers messages out to the business).

I have this set up as a filter where ‘initial request’ is on or before ‘today’ but I am now unable to use this filter as it isn’t recognised as a date. Any ideas on a workaround?

TheTimeSavingCo
Forum|alt.badge.img+28
David_Bevan wrote:

Apologies for the further query Adam but the fix here has had a knock on effect whereby the value showing in the field now is not recognised as a date. I have views set up so that records fall into different views once one of these dates are hit (this subsequently triggers messages out to the business).

I have this set up as a filter where ‘initial request’ is on or before ‘today’ but I am now unable to use this filter as it isn’t recognised as a date. Any ideas on a workaround?


Hi David, ah, could you try this instead?

IF(
  AND({Meeting Type}, Date),
  SWITCH(
    {Meeting Type},
    "Ex-Co", DATEADD(Date, -8, 'days'),
    "Board", DATEADD(Date, -11, 'days')
  )
)

It isn’t recognized as a date anymore due to the DATETIME_FORMAT() function, and so I removed that. You should be able to filter by date now

I’ve also changed it to a SWITCH() as it’s easier to read I think

Adam, Consultant @ thetimesaving.company
D
Forum|alt.badge.img+4
  • David_Bevan
  • Author
  • Participating Frequently
  • 7 replies
  • June 23, 2022
TheTimeSavingCo wrote:

Hi David, ah, could you try this instead?

IF(
  AND({Meeting Type}, Date),
  SWITCH(
    {Meeting Type},
    "Ex-Co", DATEADD(Date, -8, 'days'),
    "Board", DATEADD(Date, -11, 'days')
  )
)

It isn’t recognized as a date anymore due to the DATETIME_FORMAT() function, and so I removed that. You should be able to filter by date now

I’ve also changed it to a SWITCH() as it’s easier to read I think


Perfect, that worked and still recognises as dates for the filters, Thanks for this Adam, really appreciate you helping me out here.

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo

Reply


Most helpful members this week

Terms & Conditions