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)
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!
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!
