Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: IF formula with Workday

382 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Rlbake01
4 - Data Explorer
4 - Data Explorer

Hi all, 

I am attempting to populate a due date for when corrections on a document are needed to be done by.

A regulatory team reviews a submitted file. When it's determined that corrections are needed that team updates a single select field named "Filed by Regulatory" to show as "Corrections Requested" and adds a date/time timestamp in a field named "Reviewed Timestamp". I'd like an additional field, which can include a time or not it doesn't really matter, that will populate when the requested corrections need to be completed by - in this case 2 business days from the date it was reviewed. 

This is the formula I have so far and it seems to be working to the extent that "N/A" is populating on records that corrections were not needed, but on the records where corrections were requested-i'm not getting a date, instead i'm getting "#ERROR". 

IF({Filed by Regulatory} = "Corrections Requested",WORKDAY({Reviewed Timestamp}, 2, Holidays),"N/A")

 

Any/all help is appreciated, Thanks!!

4 Replies 4
Saravanan_009
8 - Airtable Astronomer
8 - Airtable Astronomer

Correct Formula Structure

IF(
{Filed by Regulatory} = "Corrections Requested",
WORKDAY({Reviewed Timestamp}, 2, Holidays),
"N/A"
)
Rlbake01
4 - Data Explorer
4 - Data Explorer

Hi Saravanan,

Thanks for the response. It looks like im still getting the same error with the formula you provided. 

I decided to test if it has something to do with my "Holidays" list and removed it from the formula. It is now populating something other than an error, but im not sure its useful in its current format. 

Example: The Reviewed Timestamp for a record is "8/29/2024 3:40 pm CDT" and it populated "2024-09-02T20:40:00.000Z" in the Corrections Due Date cell. I've tried formatting the result of the Corrections Due Date cell, but when I click the Formatting tab the only option it provides is to turn on "Change formula output to single select options" which would not apply at this time. 

This is the list I have for the holidays, "12/25/2023, 1/1/2024, 1/15/2024, 2/19/2024, 5/27/2024, 6/19/2024, 7/4/2024, 9/2/2024, 11/28/2024, 11/29/2024, 12/25/2024". 

Ideally, I would need a solution that doesn't result in an error and while taking into consideration the holidays listed, as well as ensuring it populates a date instead of text so that I can use the field in other functions. 

Open to ideas. 

Create a Date-Only Version of {Reviewed Timestamp} (if needed):

DATEADD({Reviewed Timestamp}, 0, 'days')

Refine the WORKDAY Formula: Use a reference to the Holidays table.

IF(
{Filed by Regulatory} = "Corrections Requested",
WORKDAY(DATEADD({Reviewed Timestamp}, 0, 'days'), 2, Holidays[Holiday Date]),
"N/A"
)

If {Reviewed Timestamp} is directly usable and contains only date values, the formula without using DATEADD would be:

IF(
{Filed by Regulatory} = "Corrections Requested",
WORKDAY({Reviewed Timestamp}, 2, Holidays[Holiday Date]),
"N/A"
)

Does this look right?  If it does, I believe it's down to the format of your holidays, as it expects it to be "YYYY-MM-DD".  Once you format your holidays correctly I think it'll work fine

Screenshot 2024-08-31 at 10.55.06 AM.png

Here's the formula I'm using:

IF(
  {Filed by Regulatory} = "Corrections Requested",
  WORKDAY({Reviewed Timestamp}, 2, Holidays)
)

You'll notice there are two records with "ERROR" there, and that's because they have no "Reviewed Timestamp" / "Holidays" value, which is causing the formula to error out.  I left it like that as I felt like it's a good indicator of missing data that needs to be filled in
Link to base