Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
times
Hello
I am trying to produce a spreadsheet that will calculate the hours people have worked and how much they have earned. There are 4 columns, Start Time, End Time, Breaks and Total I would like it to be possible to enter the Start Time , End Time and Breaks and have the Total calculated for me. So far I have achieved this, but the resulting total is still expressed as a time. For Example,if Start Time = 09:00 End Time = 17:00 Breaks = 00:30 then the Total = 07:30 indicating 7 hours 30 mins worked. However I need this to be displayed as 7.5 hours worked, in order to multiply it by the hourly rate of pay. Does anyone know a way of doing this? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
times
Angus,
The problem you are experiencing is that time is held as a fraction of 1 day. When you add/subtract time, it automatically formats the result as time, naturally. You could convert this to a decimal value by multiplying by 24. For instance if A1 holds start time, B1 end time, and C1 breaks then the decimal time worked is =(B1-A1-C1)*24 However, I would leave it as time (it looks better) and just multiple the earnings by 24. So here if D1 holds hours worked and E1 holds pay rate, then we use =D1*E1*24 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ANGUS" wrote in message ... Hello I am trying to produce a spreadsheet that will calculate the hours people have worked and how much they have earned. There are 4 columns, Start Time, End Time, Breaks and Total I would like it to be possible to enter the Start Time , End Time and Breaks and have the Total calculated for me. So far I have achieved this, but the resulting total is still expressed as a time. For Example,if Start Time = 09:00 End Time = 17:00 Breaks = 00:30 then the Total = 07:30 indicating 7 hours 30 mins worked. However I need this to be displayed as 7.5 hours worked, in order to multiply it by the hourly rate of pay. Does anyone know a way of doing this? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
times
Thank you very much indeed Bob, I would never have guessed
that in a million years! -----Original Message----- Angus, The problem you are experiencing is that time is held as a fraction of 1 day. When you add/subtract time, it automatically formats the result as time, naturally. You could convert this to a decimal value by multiplying by 24. For instance if A1 holds start time, B1 end time, and C1 breaks then the decimal time worked is =(B1-A1-C1)*24 However, I would leave it as time (it looks better) and just multiple the earnings by 24. So here if D1 holds hours worked and E1 holds pay rate, then we use =D1*E1*24 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ANGUS" wrote in message ... Hello I am trying to produce a spreadsheet that will calculate the hours people have worked and how much they have earned. There are 4 columns, Start Time, End Time, Breaks and Total I would like it to be possible to enter the Start Time , End Time and Breaks and have the Total calculated for me. So far I have achieved this, but the resulting total is still expressed as a time. For Example,if Start Time = 09:00 End Time = 17:00 Breaks = 00:30 then the Total = 07:30 indicating 7 hours 30 mins worked. However I need this to be displayed as 7.5 hours worked, in order to multiply it by the hourly rate of pay. Does anyone know a way of doing this? Thanks in advance . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate times between dates and times in Excel | Excel Discussion (Misc queries) | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) |