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

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

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

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

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

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