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!

 

[Tetra201]

Maybe

=IF(A2="","", IF(F2<>"","Scheduled",LOOKUP(NETWORKDAYS(A2,TODAY())-1,{-100000,1,2,3},{"","48 Hours to Schedule","24 Hours to Schedule","Schedule by EOB"})))

 

[C with no eyes]

Here you go:

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

Extra note:
If you also need to take bank holidays into account then put the bank holidays into a short column, say from J1:J8
Change the formula extra bit to WORKDAY(TODAY(),-1,$J$1:$J$8)

 

[C with no eyes]

I've just noticed I've been silly. Use this one:

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

 

[Tetra201]

Here you go:

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

So, on a Tuesday, your formula will return a "Schedule by EOB" for the previous Friday's date. Somehow, I don't think that's what the OP wants.

 

[C with no eyes]

Somehow, I don't think that's what the OP wants.

I must admit that when I was typing it the output didn't seem to make a great deal of sense but the OP said that the original formula provided works.

All I did was wrap a WORKDAY function around TODAY.

 

[lady_buzzkill]

So, on a Tuesday, your formula will return a "Schedule by EOB" for the previous Friday's date. Somehow, I don't think that's what the OP wants.

Actually that IS what I want, but I want it to return "Schedule by EOB" on Wednesday for the previous Friday.

Basically what this does is it lets us know how long we have left to get something scheduled. We don't count the day we received the referral, so if we got something on Friday, it would have to be scheduled by end of business on Wednesday. The current formula is fine for that during the week. The weekends are tripping me up. So this is very close to what I need.

Does this help at all?

 

[lady_buzzkill]

I'm sorry, I realized MY calculations were wrong. Having it return Schedule by EOB on a Tuesday for the previous Friday is perfect. Does it also return 24 Hours to Schedule for Monday for the previous Friday? I'm not at my work computer atm so I cannot test for myself.

 

[C with no eyes]

The edited formula I gave you is the same as the first one you provided but i changed [today] to [workday before today]

[COLOR=#333333][FONT=Tahoma]=IF(A2="","",[/FONT][/COLOR][COLOR=#333333][FONT=Tahoma]IF(F2<>"","Scheduled",[/FONT][/COLOR][COLOR=#574123]IF(A2=WORKDAY(TODAY(),-2),"24 Hours to Schedule",[/COLOR][COLOR=#574123]IF(A2=WORKDAY(TODAY(),-1),"48 Hours to Schedule",[/COLOR][COLOR=#574123]IF(A2<=WORKDAY(TODAY(),-3),[/COLOR][COLOR=#574123]"Schedule by EOB",[/COLOR][COLOR=#574123]"")))))[/COLOR]

It reads as:

=IF(A2="","",
If A2 is blank say blank

IF(F2<>"","Scheduled",
If F2 is not blank say Scheduled

IF(A2=WORKDAY(TODAY(),-2),"24 Hours to Schedule",
If A2 is two working days ago say 24 hours to schedule

IF(A2=WORKDAY(TODAY(),-1),"48 Hours to Schedule",
If A2 is one working day ago say 48 hours to schedule

IF(A2<=WORKDAY(TODAY(),-3),"Schedule by EOB",
If A2 is smaller than or equal to three working days ago say schedule by EOB

"")))))
If it does not fit any of these criteria be blank. (If there should never be a future date I would change this to "INVALID"))))) to flag incorrect entries.

Hope this helps you. Feel free to make changes to the formula so you get the output you want.

 

[C with no eyes]

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.