Time calc involving two days

[oomfh]

I know how to create time calc formulas during the same date - but not when you cross (midnight) into a second date.

Any quick examples would be greatly appreciated . . . . .

I assume there's a formula that covers both scenarios?

 

[barry houdini]

If you have start time in A2 and end time in B2 then try

=B2-A2+(A2>B2)

 

[T. Valko]

I know how to create time calc formulas during the same date - but not when you cross (midnight) into a second date.

Any quick examples would be greatly appreciated . . . . .

I assume there's a formula that covers both scenarios?

Try this...

A1= 5:00 PM
B1 = 1:00 AM

=MOD(B1-A1,1)

Format as h:mm

 

[oomfh]

Try this...

A1= 5:00 PM
B1 = 1:00 AM

=MOD(B1-A1,1)

Format as h:mm

Perfect - thank you from the south side of Pittsburgh . . .

 

[T. Valko]

Perfect - thank you from the south side of Pittsburgh . . .

You're welcome!

Lets go Pens!

 

[oomfh]

Ugh, I am now correctly calculating hours worked for each individual - but the sum of the "hours worked" column is not resulting in h:mm.

The entire column including the sum is in "13:30" time format . . .

 

[T. Valko]

Ugh, I am now correctly calculating hours worked for each individual - but the sum of the "hours worked" column is not resulting in h:mm.

The entire column including the sum is in "13:30" time format . . .

If you have a SUM(...) formula then format that cell as [h]:mm.

The brackets [ ] will keep the hours from rolling over into days.

A1 = 13:00 (h:mm format)
A2 = 12:00 (h:mm format)

With no special formatting applied to the formula cell:

=SUM(A1:A2) = 1:00

With the format of [h]:mm applied:

=SUM(A1:A2) = 25:00

 

[oomfh]

EXCEL . . . gotta luv it

Thx again

 

[oomfh]

Now I need to divide $0.00 by h:mm, i.e.,

$263.30/12:15 to get $21.49 . . . but I am gettting $5.16

In other words I need the 12 hrs 15 mins to be interpreted as 12.25

 

[jeffreybrown]

You will need to multiply the 12:15 by 24.

12:15 is actually .510417 since Excel sees a 24 hour period as 1