Calculate Due Time in Date and Hours:Minutes excluding Weekends and Public/State Holiday

[jennyzhou26]

Hi,
Just need formulas to calculate the date and time P3 and P4 work orders that have two types of deadlines.

E.g P4 have 5 business days deadline for response time, and 10 business days until it must be completed. P4 received at 24/05/17 10:30am, has Response time due on 31/05/17 10:30am, and Completion time due due 07/06/17 10:30am. I need formulas that can calculate the response time and completion time but with me manually typing in P4, 24/05/17 10:30am... We receive 50 P4 work orders a week so having it automated using excel like this would be great.

I dont want a timer, just formulas that can calculate the deadlines to the date, hour and minute

 

[Trevor_S]

5/29/17 (rather than 29/5/17) sounds like a date formatting issue ... month/day I think is a US format. Try selecting date columns, and pick a suitable format. If you can't see one, go into the custom option and enter dd/mm/yyyy hh:mm

When I try inputting that date time direct into my spreadsheet, it works. What version of excel are you using ... I'm wondering whether the workday function may differ between versions?

 

[jennyzhou26]

it might be the formatting of dates, because your sheet works and mine doesn't. I'll play with mine

 

[Trevor_S]

Apparently my link in post #18 is no longer working, here is an updated link: Date Calcs2.xlsx