Help

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

Solved
Jump to Solution
1095 4
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Bevan
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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

Screenshot 2022-06-23 at 6.40.00 PM

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.

Screenshot 2022-06-23 at 12.07.18

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.

Screenshot 2022-06-23 at 12.19.16

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

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