[bmmp]

I have 2 cells with Time Data (6AM & 6PM)
I need a formula that can calculate the number of hours within another range say 3AM to 3PM.

In other words, Between the Hours of 3AM to 3PM, how many hours did this person work if they started at 6AM and ended at 6PM.

I don't have a clue where to begin.
The answer is 9, but the answer isn't as important as the formula.

any help most appreciated!

[ruttopia]

Hmmm .. you might want to code up a simple VBA script behind the spreadsheet tied to an Update button or something to calculate the values that way. I'm not sure if you can do an in-cell formula that will handle that. Either way, it will probably be easier to maintain as a simple VBA function - the forumla could get extremely complex and might take a long time to get right.

Just my 2 cents.

[Arkham_c]

Originally posted by bmmp:
I have 2 cells with Time Data (6AM & 6PM)
I need a formula that can calculate the number of hours within another range say 3AM to 3PM.

In other words, Between the Hours of 3AM to 3PM, how many hours did this person work if they started at 6AM and ended at 6PM.

I don't have a clue where to begin.
The answer is 9, but the answer isn't as important as the formula.

any help most appreciated!

If the 2 cells are set with format of time, then this is easy.

Say you have this:

Code:
----------------------------------------
|  6:30 AM  |  8:30 PM  | =(B1-A1)*24  |
----------------------------------------

Make sure the "C1" cell is formatted as a number, not as a date.  Then you will see:

----------------------------------------
|  6:30 AM  |  8:30 PM  |    14.00     |
----------------------------------------

[nbnz]

I think you want something like this:

=(MIN(B5,B2)-MAX(A5,A2))*24

Where:
B5 = Finish Time, ie 06:00 PM
B2 = Clock Finish, ie 03:00 PM

A5 = Start Time, ie 06:00 AM
A2 = Clock Start, ie 03:00 AM

[bmmp]

thanks nbnz

but it doesn't seem to work in all situations. The actual situation I cant figure is between the hours of 4:30PM - 1:00AM how many hours worked between 6PM - 6AM [graveyard]?

Your formula worked nicely with the other example, but it doesn't seem to work in this more complex situation. It's logical though. The formula returns -17, but the real answer is 7 hours.
1AM [1:00] - 6PM [18:00]

in case your wondering, I'm trying to make a time sheet to track hours, because depending on what time it is (graveyard hours) we get paid a different rate per hour.

Here's what doesn't work
=(MIN(F13,E15)-MAX(C13,D15))*24
F13=End Time (1AM)
E15=Shift Rate Change (6AM)
C13=Start Time (4:30PM)
D15=Shift Rate Change (6PM)

[nbnz]

Well it certainly gets trickier if you are working over multiple days, but question is is it always over multiple days?

If so, you have to add 24 hours for each extra day, ie:

=((B1-A1)*24)+((MIN(B2:B3)-MAX(A2:A3))*24)

Where:
B1 = End Date (09-Dec)
A1 = Start Date (08-Dec)

It's pretty impossible to cover all scenarios you might face over an internet forum but hope this gives you the basis.