Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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



.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
subtracting time going backwards past midnight? Billman Excel Worksheet Functions 3 March 9th 08 02:21 PM
Calculating time past midnight. mailrail Excel Discussion (Misc queries) 3 April 18th 07 07:39 PM
Calculate hours and minutes between times when it goes past midnig SC_Dave Excel Worksheet Functions 9 March 23rd 07 07:19 PM
formula to calculate time difference crossing midnight ditorejax Excel Worksheet Functions 3 August 17th 06 04:46 PM
Calculating Time Past Midnight Darren Excel Worksheet Functions 6 March 13th 06 04:03 PM


All times are GMT +1. The time now is 08:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"