Skip to main content

IF formula with Workday

R
Forum|alt.badge.img+3

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

Saravanan_009
Forum|alt.badge.img+15

Correct Formula Structure

IF(
{Filed by Regulatory} = "Corrections Requested",
WORKDAY({Reviewed Timestamp}, 2, Holidays),
"N/A"
)
R
Forum|alt.badge.img+3
  • Rlbake01
  • Author
  • New Participant
  • 3 replies
  • August 30, 2024

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. 

Saravanan_009
Forum|alt.badge.img+15
Rlbake01 wrote:

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"
)
TheTimeSavingCo
Forum|alt.badge.img+28
Rlbake01 wrote:

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. 


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

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

Adam, Consultant @ thetimesaving.company

Reply


Most helpful members this week

Terms & Conditions