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 ca
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
try to convert to decimal as you instructed, a figure that woul
normally be "54:00" (hours worked) turns to "-141.50" Any ideas? Als
is there anyway the formula can allow for text in a cell ie "DAY OFF
with returning a #value error?

Cheers!

Bank

 

[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 cell
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 repor
7 hours in the sheet.
I have a sheet that calculates the expected hours per month, and i hav
to fill in the actual declarable hours. As soon as the actual hours ar
less than expected the formula which deducts the worked hours from th
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 th
result is contained in G16 an option is given below although the answe
is in decimal hours and is a text string so it depends what you wish t
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 fo
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 star
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 les
than 8 hours (e.g 09:00-13:00 or 10:00-14:00) that it does not deduc
an hour for lunch.
Cheers!

Bank

 

[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))*2