Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtracting time going backwards past midnight? | Excel Worksheet Functions | |||
Calculating time past midnight. | Excel Discussion (Misc queries) | |||
Calculate hours and minutes between times when it goes past midnig | Excel Worksheet Functions | |||
formula to calculate time difference crossing midnight | Excel Worksheet Functions | |||
Calculating Time Past Midnight | Excel Worksheet Functions |