Using IFS to calculate a task due by date but needing to remove weekends

[StephMJ]

Hi there,

I am trying to calculate a DUE BY date/time based on PRIORITY of task completion from TIME OF REQUEST, and am using the following formula:

=IFS(OR(ISBLANK(A4),ISBLANK(B4)),"",B4=Data!$N$10,A4+4/24,B4=Data!$N$11,A4+1,B4=Data!$N$12,A4+5)

A4 = Time of request
B4 = Priority (i.e. task to be completed within 4 hours, 24 hours or 5 working days from time of request - these options are presented in a drop down menu)

Time of request	Priority​	DUE BY​
17/04 12:31	Very Urgent (<4 hrs)	17/04 16:31
17/04 13:30	Urgent (24 hrs)	18/04 13:30
19/04 14:00	5 Business Days	24/04 14:00

This formula seems to work pretty well, however, I need to remove weekends from the calculation (otherwise the DUE BY includes days where no one is working). I've tried playing around with WORKDAY and NETWORKDAYS but can't make it work (if anything, it stuffs up the formula altogether or leads to an error statement). This self-trained excel novice is at a loss, so any advice on how I can make this work would be greatly appreciated Thank you!

 

[awoohaw]

I'm not sure where your error is. But, I changed the formula a bit, and am using the SWITCH function:
(You should prob also use the WORKDAY function for the 2nd scenario as well, ... what happens if column A is a Friday?

Book1
	A	B	C									L	M	N
1
2
3	Time of request	Priority	DUE BY
4	2024-04-17 12:31	Very Urgent (<4 hrs)	2024-04-17 16:31									2024-04-17 16:31		Very Urgent (<4 hrs)
5	2024-04-17 13:30	Urgent (24 hrs)	2024-04-18 13:30									2024-04-18 13:30		Urgent (24 hrs)
6	2024-04-19 14:00	5 Business Days	2024-04-24 14:00									2024-04-26 14:00		5 Business Days
Sheet1

Cell Formulas
Range	Formula
L4:L6	L4	=IF(OR(ISBLANK(A4),ISBLANK(B4)),"",SWITCH(B4,$N$4,A4+4/24,$N$5,A4+1, (A4-INT(A4)) + WORKDAY.INTL(A4,5,1)))

 

[StephMJ]

I'm not sure where your error is. But, I changed the formula a bit, and am using the SWITCH function:
(You should prob also use the WORKDAY function for the 2nd scenario as well, ... what happens if column A is a Friday?

Book1
	A	B	C									L	M	N
1
2
3	Time of request	Priority	DUE BY
4	2024-04-17 12:31	Very Urgent (<4 hrs)	2024-04-17 16:31									2024-04-17 16:31		Very Urgent (<4 hrs)
5	2024-04-17 13:30	Urgent (24 hrs)	2024-04-18 13:30									2024-04-18 13:30		Urgent (24 hrs)
6	2024-04-19 14:00	5 Business Days	2024-04-24 14:00									2024-04-26 14:00		5 Business Days
Sheet1

Cell Formulas
Range	Formula
L4:L6	L4	=IF(OR(ISBLANK(A4),ISBLANK(B4)),"",SWITCH(B4,$N$4,A4+4/24,$N$5,A4+1, (A4-INT(A4)) + WORKDAY.INTL(A4,5,1)))

Amazing! Thank you so much - I appreciate you taking the time to provide this. It works a treat! I have much to learn about excel...

 

[awoohaw]

You're welcome. And welcome to the forum
Best Wishes!