Calculate hours, past midnight etc
 

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

Calculate hours, past midnight etc
 

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


"CindyJ" wrote in message
...
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

Calculate hours, past midnight etc
 

Try this...

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

Format as General or Number

--
Biff
Microsoft Excel MVP


"CindyJ" wrote in message
...
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

Calculate hours, past midnight etc
 

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

"T. Valko" wrote:

Try this...

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

Format as General or Number

--
Biff
Microsoft Excel MVP


"CindyJ" wrote in message
...
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



.

Calculate hours, past midnight etc
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"CindyJ" wrote in message
...
Thank you - this seems to do the trick!! :-)

"T. Valko" wrote:

Try this...

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

Format as General or Number

--
Biff
Microsoft Excel MVP


"CindyJ" wrote in message
...
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



.