# Excel Formula to Calculate Time Span Between Different Time Range.

1. ## Excel Formula to Calculate Time Span Between Different Time Range.

Hi;

I am trying to calculate hours worked if time span fall between different time range.

Range 1 Range 2 Range 3
Start Time Finish Time Total Hours >14:00 and <16:00 >16:00 and <22:00 >22:00
08:00 14:00 6 0 0 0
08:00 15:00 7 1 0 0
08:00 18:00 10 2 2 0
10:00 17:00 7 2 1 0
10:00 23:00 13 2 6 1
12:00 02:00 14 2 6 4

I can get the total hours worked by using the MOD Function = MOD(B2-A2,1)*24 but struggling on how to calculate number of hours if finish time falls between different time range.

Any help will be much appreciated.

Thanks
Omer.  Register To Reply

2. ## Re: Excel Formula to Calculate Time Span Between Different Time Range.

If you have total hours in C2 try this formula in D2 for hours between 14:00 and 16:00

=MAX(0,MIN(B2,"16:00")-MAX(A2,"14:00"))*24

and similar for 16:00 to 22:00

=MAX(0,MIN(B2,"22:00")-MAX(A2,"16:00"))*24

Then for hours after 22:00

=MAX(0,MIN(B2+(B2<A2),1+(B2<A2))-MAX(A2,"22:00"))*24  Register To Reply

3. ## Re: Excel Formula to Calculate Time Span Between Different Time Range.

Thanks Daddylonglegs, much appreaciates. It works great.

The only trouble i am having now is if it overlaps to next day, finding it difficult to get it to work.

Following is what i am trying to achieve;

Range 4 Range 5
Start Time Finish Time Total Hours >23:00 and < 01:00 >01:00
12:00 04:00 16 2 3

Any help on this will be much appreciated.

Thanks.  Register To Reply

4. ## Re: Excel Formula to Calculate Time Span Between Different Time Range.

overlaps to next day
use 28:00 hour with celformat h:mm you see 4:00
You can calculate with >23:00  Register To Reply

5. ## Re: Excel Formula to Calculate Time Span Between Different Time Range.

Thanks Popi, much appreciated. Your solution is great and It could do the job but I will need to create a helper column for it.

Currently Data Entry are done on the Start and Finish Time so was hoping to find a formulae based solution rather than using a helper columns.

Thanks.
Omer.  Register To Reply

6. ## Re: Excel Formula to Calculate Time Span Between Different Time Range.

You don't need a helper column.  Register To Reply

7. ## Re: Excel Formula to Calculate Time Span Between Different Time Range. ``Please Login or Register  to view this content.``
Copy down, format as Number.

Assumes no one will work past 0800 the following morning. Change as required.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 