Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
Hi There, I have just created a schedule spreadsheet, does anybody know a formula that will allow me to add up the hours an employee has worked each week based on the shift times written in the cell for each day. e.g. cell A1 is "09.00-18.00" which equals 8 hours work, cell B2 is "08.00-20.00" which equals 11 hours work. I need a formula that will add A1 and B2 and just show the number 19. Cheers BankC -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
You can always just split your times into 2 different cells like 9 am in one and then their closing time in the other. That way you can subtract that and have them add at the bottom -- Coltsfan ------------------------------------------------------------------------ Coltsfan's Profile: http://www.excelforum.com/member.php...o&userid=19982 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
Thanks but I'm really hoping that there is a formula out there that can calculate this. Anybody? -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
Is that exactly the format you're using? It's possible to calculate with a formula for instance for A1 =(SUBSTITUTE(RIGHT(A1,5),".",":")-SUBSTITUTE(LEFT(A1,5),".",":"))*24-1 to show the hours in decimal format but, as you can tell that will get a little complicated, especially if your cells are not contiguous, e.g. A1 & B2 as per your example. It's much easier if you enter your times separately and in a recognisable time format, e.g. 08:00 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
I can easily change the format to be "09:00-18:00", what would the formula be then or is that it -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
That makes it a bit easier To show the result in time format you could use =SUM(IF(A1:G1<"",RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24)) confirmed with CTRL+SHIFT+ENTER format as [h]:mm to show in decimals multiply the above by 24 and format as general or number PS I was going to suggest a SUMPRODUCT formula which doesn't require CTRL+SHIFT+ENTER but this one only works if you have time entries in all 7 cells =SUMPRODUCT(RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24)*24 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
Hi, thanks for the formula. It works fine in hours format but when I try to convert to decimal as you instructed, a figure that would normally be "54:00" (hours worked) turns to "-141.50" Any ideas? Also is there anyway the formula can allow for text in a cell ie "DAY OFF" with returning a #value error? Cheers! BankC -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
I cant see why the decimals won't work if the hours does, it works for me:) If you want to ignore text use =SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24))*24 confirmed with CTRL+SHIFT+ENTER format as general or number note: that this won't cope with "night shifts", i.e. days that start before but end after midnight, e.g. 23:00 - 07:00 - for that amend to =SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24+(LEFT(A1:G1,5)RIGHT(A1:G1,5))))*24 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
The formula has worked a treat, I must have not had all of the cells formatted correctly on my earlier attempt. Thanks so much! -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
Hope someone is stil reading this thread! What happens if you are is supposed to work for 8 hours but only report 7 hours in the sheet. I have a sheet that calculates the expected hours per month, and i have to fill in the actual declarable hours. As soon as the actual hours are less than expected the formula which deducts the worked hours from the expected hours turns out #########. Negative hours not possible. Is there a method to show the number of hours that are short? -- reinold ------------------------------------------------------------------------ reinold's Profile: http://www.excelforum.com/member.php...o&userid=31006 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
I am not able to see your formula that does the calculation, but if the result is contained in G16 an option is given below although the answer is in decimal hours and is a text string so it depends what you wish to do with it =IF(G160,G16,(TEXT(G16*24,"0.00"))) -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
daddylonglegs Wrote: I cant see why the decimals won't work if the hours does, it works for me:) If you want to ignore text use =SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24))*24 confirmed with CTRL+SHIFT+ENTER format as general or number note: that this won't cope with "night shifts", i.e. days that start before but end after midnight, e.g. 23:00 - 07:00 - for that amend to =SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-1/24+(LEFT(A1:G1,5)RIGHT(A1:G1,5))))*24 Is it possible to adjust your formula so that if there is a shift less than 8 hours (e.g 09:00-13:00 or 10:00-14:00) that it does not deduct an hour for lunch. Cheers! BankC -- BankC ------------------------------------------------------------------------ BankC's Profile: http://www.excelforum.com/member.php...o&userid=26337 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to calculate hours worked
It make the formula a bit longer but try =SUM(IF(ISNUMBER(LEFT(A1:G1,5)+0),RIGHT(A1:G1,5)-LEFT(A1:G1,5)-(RIGHT(A1:G1,5)-LEFT(A1:G1,5)=1/3)*1/24))*24 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=501797 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating numbers of hours worked in pay divisions | Excel Discussion (Misc queries) | |||
How do I write the formula to calculate someones time worked | Excel Worksheet Functions | |||
How do I calculate hours in Excel | New Users to Excel | |||
How to calculate hours? | Excel Worksheet Functions | |||
Excel needs hh:mm where hh>24 for hours worked per week | Excel Worksheet Functions |