Calculate hours, past midnight etc

[CindyJ]

Need help on formula calculating start time, end time, start time, end time -
with hours past midnight. What I have so far:

C3 D3 E3 F3 G3
START TIME END TIME START TIME END TIME TOTAL HRS
3:00 PM 8:00 PM 9:00 PM 12:00 AM -16.00 (should be 8
hrs worked)

or

C3 D3 E3 F3 G3
START TIME END TIME START TIME END TIME TOTAL HRS
11:00 PM 7:00 AM -16.00
(should be 8 hrs worked)

G3's formula:
=ROUND(((D3-C3)+(F3-E3))*24.2)

I've attempted to format the work times as [h]:mm but it just converts the
hours to 24 hour time (which isn't used here) - am I doing some incorrect
with that?

Thank you in advance - I've been reading past threads all afternoon but
nothing I've found quite helps yet.

Using Microsoft Excel 2007

 

[Bernie Deitrick]

Cindy,

You need to check for the last time being earlier in the day than the third
time, and add 1 to account for that

=ROUND(((D3-C3)+(F3-E3+IF(F3<E3,1,0)))*24,2)

which can also be simplified to just

=ROUND(((D3-C3)+(F3-E3+(F3<E3)))*24,2)

You could also use the same for the first two times, in case they might be
on apposite sides of midnight as well.

=ROUND(((D3-C3 + IF(D3<C3,1,0))+(F3-E3+IF(F3<E3,1,0)))*24,2)


HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try this...

=ROUND(SUM(MOD(D3-C3,1),MOD(F3-E3,1))*24,2)

Format as General or Number

 

[CindyJ]

Thank you - this seems to do the trick!!

Try this...

=ROUND(SUM(MOD(D3-C3,1),MOD(F3-E3,1))*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP

Need help on formula calculating start time, end time, start time, end
time -
with hours past midnight. What I have so far:

C3 D3 E3 F3
G3
START TIME END TIME START TIME END TIME TOTAL HRS
3:00 PM 8:00 PM 9:00 PM 12:00 AM -16.00 (should be
8
hrs worked)

or

C3 D3 E3 F3
G3
START TIME END TIME START TIME END TIME TOTAL HRS
11:00 PM 7:00 AM -16.00
(should be 8 hrs worked)

G3's formula:
=ROUND(((D3-C3)+(F3-E3))*24.2)

I've attempted to format the work times as [h]:mm but it just converts the
hours to 24 hour time (which isn't used here) - am I doing some incorrect
with that?

Thank you in advance - I've been reading past threads all afternoon but
nothing I've found quite helps yet.

Using Microsoft Excel 2007

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you - this seems to do the trick!!

Try this...

=ROUND(SUM(MOD(D3-C3,1),MOD(F3-E3,1))*24,2)

Format as General or Number

--
Biff
Microsoft Excel MVP

Need help on formula calculating start time, end time, start time, end
time -
with hours past midnight. What I have so far:

C3 D3 E3 F3
G3
START TIME END TIME START TIME END TIME TOTAL HRS
3:00 PM 8:00 PM 9:00 PM 12:00 AM -16.00 (should
be
8
hrs worked)

or

C3 D3 E3 F3
G3
START TIME END TIME START TIME END TIME TOTAL HRS
11:00 PM 7:00 AM -16.00
(should be 8 hrs worked)

G3's formula:
=ROUND(((D3-C3)+(F3-E3))*24.2)

I've attempted to format the work times as [h]:mm but it just converts
the
hours to 24 hour time (which isn't used here) - am I doing some
incorrect
with that?

Thank you in advance - I've been reading past threads all afternoon but
nothing I've found quite helps yet.

Using Microsoft Excel 2007

.