Calculating working days and working hours between two dates

[SJV9]

How should I calculate working days, hours and minutes between two dates?I am creating a 'System Outage Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days, hours and minutes are between the two date/time stamps to determine lost production hours given the ff.:

Variables:
- 'Start time' (A1)
- 'Time resolved' (B1)
- 'Duration' (C1)

Constants:
Work Days = Monday to Friday
Work Hrs = 6:30 AM to 3:30 PM (1 hour 30 minute lunch break)
Public Holidays = (AA1:AA30)

Formats:
Start time: dd/mm/yyyy hh:mm AM/PM
Time resolved: dd/mm/yyyy hh:mm AM/PM
Duration: d "Days" hh:mm


Any help would be greatly appreciated!

SJV9

 

[barry houdini]

Assuming the start and end time/dates are always within working hours you can use this formula

=(NETWORKDAYS(A1,B1,AA$1:AA$30)-1)-(MOD(A1,1)>MOD(B1,1))&" days "&TEXT(MOD(MOD(B1,1)-MOD(A1,1)+((MOD(B1,1)<"10:30"+0)-(MOD(A1,1)<"10:30"+0))*"1:30","7:30"),"hh:mm")

Assumes lunch starts at 10:30, change as required

 

[SJV9]

Hi Barry! Thing is the start time/date is always within working hours and end time/date depends on when the system issue was resolved by the helpdesk so it can be outside of our working hours so I need to compute how much of our production time is lost/wasted during business operation hours only (6:30am - 3:30pm).

 

[SJV9]

Hi Barry! Thing is the start time/date is always within working hours and end time/date depends on when the system issue was resolved by the helpdesk so it can be outside of our working hours so I need to compute how much of our production time is lost/wasted during business operation hours only (6:30am - 3:30pm).


STRAT END DURATION SHOULD BE
8/1/2011 6:30 8/1/2011 22:30 0 days 07:00 WRONG 0 days 7:30
8/1/2011 6:30 8/2/2011 17:00 1 days 01:30 WRONG 2 Days 0:00
8/1/2011 6:30 8/1/2011 14:30 0 days 06:30 CORRECT 0 days 6:30
8/1/2011 6:30 8/8/2011 15:30 5 days 00:00 WRONG 6 Days 0:00
8/1/2011 6:30 8/8/2011 7:00 5 days 00:30 CORRECT 5 Days 0:30