| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
In Excell how can i get a total hours worked value from 2 times?
if i start work at 7am and leave work at 5pm
how do i calculate the time worked using a formular? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 times?
Try this:
A1 = 7:00 AM B1 = 5:00 PM D1 = formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)) Format the formula cell as h:mm. Result = 10:00 If you want the result as a decimal then use this formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24) Format the formula cell as GENERAL or NUMBER. Result = 10 -- Biff Microsoft Excel MVP "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Why bother with MOD function. Keeep it simple
It should be suffice =IF(COUNT(A1:B1)<2,"",B1-A1) "T. Valko" wrote: Try this: A1 = 7:00 AM B1 = 5:00 PM D1 = formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)) Format the formula cell as h:mm. Result = 10:00 If you want the result as a decimal then use this formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24) Format the formula cell as GENERAL or NUMBER. Result = 10 -- Biff Microsoft Excel MVP "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Did you read my post?
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Teethless mama" wrote in message ... Why bother with MOD function. Keeep it simple It should be suffice =IF(COUNT(A1:B1)<2,"",B1-A1) "T. Valko" wrote: Try this: A1 = 7:00 AM B1 = 5:00 PM D1 = formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)) Format the formula cell as h:mm. Result = 10:00 If you want the result as a decimal then use this formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24) Format the formula cell as GENERAL or NUMBER. Result = 10 -- Biff Microsoft Excel MVP "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Why bother with MOD function.
Since we don't know what kind of times will be used we can cover all hours by using MOD or the other variation: =IF(COUNT(A1:B1)<2,"",B1-A1+(B1A1)) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Why bother with MOD function. Keeep it simple It should be suffice =IF(COUNT(A1:B1)<2,"",B1-A1) "T. Valko" wrote: Try this: A1 = 7:00 AM B1 = 5:00 PM D1 = formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)) Format the formula cell as h:mm. Result = 10:00 If you want the result as a decimal then use this formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24) Format the formula cell as GENERAL or NUMBER. Result = 10 -- Biff Microsoft Excel MVP "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Typo in the formula:
=IF(COUNT(A1:B1)<2,"",B1-A1+(B1A1)) Should be: =IF(COUNT(A1:B1)<2,"",B1-A1+(B1<A1)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Why bother with MOD function. Since we don't know what kind of times will be used we can cover all hours by using MOD or the other variation: =IF(COUNT(A1:B1)<2,"",B1-A1+(B1A1)) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Why bother with MOD function. Keeep it simple It should be suffice =IF(COUNT(A1:B1)<2,"",B1-A1) "T. Valko" wrote: Try this: A1 = 7:00 AM B1 = 5:00 PM D1 = formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)) Format the formula cell as h:mm. Result = 10:00 If you want the result as a decimal then use this formula: =IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24) Format the formula cell as GENERAL or NUMBER. Result = 10 -- Biff Microsoft Excel MVP "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 times?
To start, you must use XL recognizable times.
You'll need the colon ( : ) for hours and minutes; 7:30 15:45 For whole hours, XL helps with allowing you to use; 7<spacea to get 7:00 AM OR 11<spacep to get 11:00 PM With start time in A2: 7:00 AM And end time in B2: 5:00 PM Enter this formula in C2 =(B2-A2)*24 And format C2 to General or Number to display the hours worked. If you times might span midnight, use this formula in C2: =MOD(B2-A2,1)*24 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ronnie Kray" <Ronnie wrote in message ... if i start work at 7am and leave work at 5pm how do i calculate the time worked using a formular? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Thanks Guys that works a treet but no i have a problem working out the Totla
Hour worked Per Week mon A1 07:00 Stasrt B1 17:30 Finish C1 10:30 (hours worked With your previous formulars) Tue A2 07:00 Start B2 16:45 Finish C2 9:45 Hours So how do i add C1 & C2 to show total hours which should be 20 Hours 15 Mins |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Try this:
=SUM(C1:C2) Format as [h]:mm -- Biff Microsoft Excel MVP "Ronnie Kray" wrote in message ... Thanks Guys that works a treet but no i have a problem working out the Totla Hour worked Per Week mon A1 07:00 Stasrt B1 17:30 Finish C1 10:30 (hours worked With your previous formulars) Tue A2 07:00 Start B2 16:45 Finish C2 9:45 Hours So how do i add C1 & C2 to show total hours which should be 20 Hours 15 Mins |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
=SUM(C1:C2)
this works fine for 2 cells how do i add C1 C2 C3 C4 C5 if all daily hors are 10:00 i need it to show a total of 50:00 hours worked ps sorry for the lame questions but its 18 years since i last worked on excell at school thx ronnie "T. Valko" wrote: Try this: =SUM(C1:C2) Format as [h]:mm -- Biff Microsoft Excel MVP "Ronnie Kray" wrote in message ... Thanks Guys that works a treet but no i have a problem working out the Totla Hour worked Per Week mon A1 07:00 Stasrt B1 17:30 Finish C1 10:30 (hours worked With your previous formulars) Tue A2 07:00 Start B2 16:45 Finish C2 9:45 Hours So how do i add C1 & C2 to show total hours which should be 20 Hours 15 Mins |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
=SUM(C1:C2)
this works fine for 2 cells how do i add C1 C2 C3 C4 C5 =SUM(C1:C5) -- Biff Microsoft Excel MVP "Ronnie Kray" wrote in message ... =SUM(C1:C2) this works fine for 2 cells how do i add C1 C2 C3 C4 C5 if all daily hors are 10:00 i need it to show a total of 50:00 hours worked ps sorry for the lame questions but its 18 years since i last worked on excell at school thx ronnie "T. Valko" wrote: Try this: =SUM(C1:C2) Format as [h]:mm -- Biff Microsoft Excel MVP "Ronnie Kray" wrote in message ... Thanks Guys that works a treet but no i have a problem working out the Totla Hour worked Per Week mon A1 07:00 Stasrt B1 17:30 Finish C1 10:30 (hours worked With your previous formulars) Tue A2 07:00 Start B2 16:45 Finish C2 9:45 Hours So how do i add C1 & C2 to show total hours which should be 20 Hours 15 Mins |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
In Excell how can i get a total hours worked value from 2 time
Under normal circumstances, the total time is subsequently multiplied by the
hourly rate to calculate the total monies due. That is why, in my post, I suggested that you format the Column C cells to Number or General. That allows the totals to return a dollar amount when multiplied by the rate. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ronnie Kray" wrote in message ... =SUM(C1:C2) this works fine for 2 cells how do i add C1 C2 C3 C4 C5 if all daily hors are 10:00 i need it to show a total of 50:00 hours worked ps sorry for the lame questions but its 18 years since i last worked on excell at school thx ronnie "T. Valko" wrote: Try this: =SUM(C1:C2) Format as [h]:mm -- Biff Microsoft Excel MVP "Ronnie Kray" wrote in message ... Thanks Guys that works a treet but no i have a problem working out the Totla Hour worked Per Week mon A1 07:00 Stasrt B1 17:30 Finish C1 10:30 (hours worked With your previous formulars) Tue A2 07:00 Start B2 16:45 Finish C2 9:45 Hours So how do i add C1 & C2 to show total hours which should be 20 Hours 15 Mins |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate total pay from pay rate times hours and minutes | Excel Discussion (Misc queries) | |||
| Calculate Total hours worked during workdays within given shift time. | Excel Discussion (Misc queries) | |||
| total hours vs total worked hours | Excel Discussion (Misc queries) | |||
| multiply hours/minutes times dollars for total dollar amount | Excel Discussion (Misc queries) | |||
| Getting a total of hours & mins worked per week | New Users to Excel | |||
Hybrid Mode
