Calculating Time Intervals

**JustMe602** · 05-23-2007, 12:47 AM

Okay please notice the time spans several days I would like to get the total amount of hours eclipsed from the start time to the end time.

Any help would be apprieciated.

HTML Code:

**Paul** · 05-23-2007, 01:02 AM

If you don't care about business hours, weekends, holidays, etc.. just straight up how many hours are between these two times, use this formula:

=IF(B2="","",(B2-A2)*24)

If B2 is blank, you obviously don't want to subtract A2 from blank, hence the IF formula.

This will give you a result like 823.20 hours, not 823 hours 12 minutes. (Although you could use custom formatting to make it look differently.)

**JustMe602** · 05-23-2007, 11:32 AM

Can someone also help with omitting specific days and only getting work days?

Since we work sometimes on Saturday can we identify this in the formula as well as we don't always have a specific 8-5 work day. So again if I can set those times that would be helpful. But if not I can use the standard info of no weekends workday 8-5.

Thanks in advance for the information

**Mark@Work** · 05-23-2007, 11:54 AM

The WEEKDAY() function will tell you what day any particular date falls on.

Knowing the day of the week for the first day and for the last day and the total number of days between them it is possible to work out how many saturdays and sundays fall between these two dates.

Also the NETWORKDAYS() will tell you how many days there are between two dates without counting Saturdays and Sundays.

subtracting the NETWORKDAYS() from the total number of days will tell you how many "non-working" days were worked

**daddylonglegs** · 05-23-2007, 07:55 PM

If you just want to count 08:00 to 17:00 weekdays and A2 contains start time/date and B2 end time/date then, assuming the start and end will always be within those business hours you can calculate the business hours using this formula

=(NETWORKDAYS(A2,B2)-1)*3/8+MOD(B2,1)-MOD(A2,1)

format as [h]:mm