Calculate hours, past midnight etc

  • Thread starter Thread starter CindyJ
  • Start date Start date
C

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
 
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
 
Thank you - this seems to do the trick!! :-)

T. Valko said:
Try this...

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

Format as General or Number

--
Biff
Microsoft Excel MVP


CindyJ said:
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


.
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


CindyJ said:
Thank you - this seems to do the trick!! :-)

T. Valko said:
Try this...

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

Format as General or Number

--
Biff
Microsoft Excel MVP


CindyJ said:
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


.
 
Back
Top