I`m trying to calculate the date ( with hours minutes and seconds) when a ticket will breach the 9 business hours mark.
I have the following: work start hour is 8AM, work end hour is 5PM. Week is Monday to Friday. I also have a list of holidays based country. I can have start date in weekends, holidays, before start time and also after start.
I`m using this formula
=WORKDAY(A1,1+INT((9-(17-MOD(A1,1)*24))/8),INDIRECT(CONCATENATE("'Validation'!j",MATCH(B1,Validation!I:I,0),":j",MATCH(B1,Validation!I:I,0))))+TIME(8,0,0)+MOD((9-(17-MOD(A1,1)*24)),8)/24
A1 = Start time
9 = Number of hours to add
B1 = Country name
In another tab I have the list of countries and holidays, indirect/concatenate/match formula is helping me identify the start row and ending row of the holidays per the respective country.
It works fine for start times between 8am and 5pm I think time (8,0,0) is the start time but I`m not sure what " 8" after mod formula does.
Got this formulas from here http://answers.microsoft.com/en-us/office/forum/officeversion_other-excel/add-work-hours-to-a-date-and-time/c41cf049-3797-48a3-8156-438401c6137c?page=2
This formula doesnt take into account start time outside business hours.
If I receive a ticket on 24th march at 8pm and 25&28 are holidays, I would expect that by adding 9 business hours i get 29 march 5PM. if I receive a ticket on 25th at 5AM, I would expect to same result if I added 9 business hours.
Thanks, sorin
