CODEWITHSUNDEEP
excelexcel-formula
roberth
roberth

Reputation: 47

Weekday and Workday Formula gives Value Error in terms of calculating actual due dates

I'm working on calculating our work completion dates, and I want to think I have a pretty good understanding of IF statements, but I can't seem to figure this one out at all.

So we have three products: policy, quotes and rating, and they each have their own service times/dates process. - If a policy is requested before 4 pm, then it has to be completed the same day, otherwise if it comes in after 4:00 pm, then you have till 11:59 the next day. - With quotes you have 48 hours to complete them, and with rating you have 7 days.

I also have a lookup sheet which references company holidays, and I decided to use workday function because some services get in on Fridays, and we are only count weekdays when calculating our due dates. I have also attached my formula in text below, do you guys think that I'm messing up in terms of how I am using both (OR) and (AND) functions to nest the IF statement?

=IF(OR(WEEKDAY(IF(A2="Quote",D2+2,IF(A2="Rating",Q2+7,"")))=1,WEEKDAY(IF(A2="Quote",D2+2,IF(A2="Rating",D2+7,""))=7),
IF(A2="Quote",D2+2,IF(A2="Rating",D2+7,""))+2,
IF(A2="Quote",D2+2,IF(A2="Rating",D2+7,""))),
IF(OR(A2="Policy",HOUR(C2<=16),INT(C2)+TIME(23,59,0)),
IF(OR(A2="Policy",HOUR(C2)>16),WORKDAY(D2,1,Lookup!$M$2:$M$20)+TIME(23,59,0),"ERROR"))

Photo of work and columns

Upvotes: 2

Views: 799

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4834

If I've understood your requirements correctly, then you should be able to use =IF(A2="Policy",IF(HOUR(C2)<16,D2,WORKDAY(D2,1)), WORKDAY(D2,IF(A2="Quote",1,IF(A2="Rating",7,0))))

That just returns a date. If you want it to return a datetime, then use this:

=IF(A2="Policy",IF(HOUR(C2)<16,D2+1,WORKDAY(D2,1)+1)-1/1440, WORKDAY(D2,IF(A2="Quote",1,IF(A2="Rating",7,0)))+TIME(HOUR(C2),MINUTE([StartTime]),0))

...which returns this:

enter image description here

The Due By column has a custom number format applied of mmmm d, yyyy h:mm AM/PM

Upvotes: 1

Related Questions