Conditional WORKDAY formula

[rizwanulhasan]

Hi,

In the attached excel file, i want exact 1 day to be added to the date in cell C2 i.e. 24 hrs excluding the holidays mentioned in Sheet 1.

However, the time i receive in the cell D2 is always 12:00 AM whereas I want it to be 6:00 PM. i.e. date should change but the time remains as in the cell C2.

Can some one please guide with the correct formula.

Thanks in advance.

[Glenn Kennedy]

One way:

=IF((AND(A2="Incident",B2="High")),WORKDAY(C2,1,Sheet1!$B$3:$B$19)+MOD(C2,1),IF((AND(A2="Request",B2="High")),WORKDAY(C2,5,Sheet1!$B$3:$B$19)+MOD(C2,1)))

[AliGW]

Try this:

=IF((AND(A2="Incident",B2="High")),WORKDAY(C2,1,Sheet1!$B$3:$B$19)+MOD(C2,1),IF((AND(A2="Request",B2="High")),WORKDAY(C2,5,Sheet1!$B$3:$B$19)+MOD(C2,1)))

[Glenn Kennedy]

Since B2 does not influence the outcome, you could also use:

=SUM(IF(A2={"Incident","Request"},WORKDAY(C2,{1,5},Sheet1!$B$3:$B$19)+MOD(C2,1),0))

[rizwanulhasan]

Hi Glenn Kennedy and AliGW,

Thank you both for the speedy response.

However, there's one minor issue.

If the Creation Date (C3) is 02-Jan-2022 02:00 PM which falls on a weekend/holiday, the Deadline (D3) is 04-Jan-2022 02:00 PM. In such a scenario, the time provided is just 2 hours where in fact it should be 24 hours. Here, the working day should start from 04-Jan-2022 thereby giving the deadline as 05-Jan-2022 02:00 PM.

Is there any way that excel doesn't consider the weekend/holiday as the initial day in this calculation. Also just to inform, the working hours are 12:00 PM to 09:00 PM.

Thanks in advance.

[Glenn Kennedy]

Hi. You are introducing additional criteria. So, please take a moment to update your sheet to cover ALL possible sceanrios, complete with expected answers.

[rizwanulhasan]

Hi. You are introducing additional criteria. So, please take a moment to update your sheet to cover ALL possible sceanrios, complete with expected answers.

Hi Glenn,

Sorry for the earlier incomplete post.

You will find the details in the attached excel file. The deadline is to be set based on the category/priority as given in the adjacent table.

All is perfect if the creation date is a working day. However, if its a holiday, we are getting a problem with the deadline.

Could you please help in this regard.

Thank you.

[Glenn Kennedy]

Please add sample dates and EXPECTED answers, as requested!!