Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Coltsfan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
reinold
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dav
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
BankC
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default 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
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
Calculating numbers of hours worked in pay divisions andyiain Excel Discussion (Misc queries) 1 December 22nd 05 12:28 PM
How do I write the formula to calculate someones time worked deusy Excel Worksheet Functions 3 November 16th 05 09:49 PM
How do I calculate hours in Excel Bobbi Prochnow [MSFT] New Users to Excel 0 October 31st 05 08:51 PM
How to calculate hours? cheryl Excel Worksheet Functions 6 August 2nd 05 08:20 AM
Excel needs hh:mm where hh>24 for hours worked per week pmba Excel Worksheet Functions 2 May 16th 05 09:03 PM


All times are GMT +1. The time now is 09:53 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"