Need formula to calculate hours worked

[BankC]

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

[Coltsfan]

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

[BankC]

Thanks but I'm really hoping that there is a formula out there that can calculate this. Anybody?

[daddylonglegs]

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

[BankC]

I can easily change the format to be "09:00-18:00", what would the formula be then or is that it

[daddylonglegs]

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

[BankC]

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

[daddylonglegs]

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

[BankC]

The formula has worked a treat, I must have not had all of the cells formatted correctly on my earlier attempt.

Thanks so much!

[reinold]

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?

[Dav]

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(G16>0,G16,(TEXT(G16*24,"0.00")))

[BankC]

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

[daddylonglegs]

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