Merging NETWORKDAYS and IF statements

[lady_buzzkill]

Hello everyone, I'm working on a spreadsheet for my job that will display the amount of time remaining to schedule an appointment or complete a report. The formula is working beautifully, with the exception of not being able to account for weekends and holidays. Can someone help me find a way to nest a NETWORKDAYS or other formula into my current string of IF formulas to get this working?

Please note that I haven't touched Excel in over a decade, so if my formula looks convoluted and you can streamline it, feel free. Here is what I currently have:

=IF(ISBLANK(A2),"", IF(NOT(ISBLANK(F2)),"Scheduled",IF(A2=(TODAY()-2),"24 Hours to Schedule",IF(A2=(TODAY()-1),"48 Hours to Schedule",IF(A2<=(TODAY()-3),"Schedule by EOB","")))))

Thanks!

 

[lady_buzzkill]

Oh, and don't forget if you need extra holidays, like bank holidays then in the WORKDAY functions you will need to add the range for the additional dates.

Make sure you put $ before the columns and rows in the range e.g. WORKDAY(TODAY(),-2,$J$1:$J$8) so that when you drag the formula the cell references will not change.

C, you have been incredibly helpful! I have another question - when using this function for the holidays, do I need to add the $J$1:$J$8 after each occurrence of WORKDAY(TODAY)() ? Since I have three of those lines in my formula, I want to make sure that I'm doing it correctly. Everything is working -PERFECTLY- right now and I'm so very grateful. This is the final step to make it 100%!

 

[C with no eyes]

Yes. Change all three WORKDAY functions to include the range for your extra dates.

You're very welcome.