software

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 quot;09.00-18.00quot; which equals 8 hours work, cell B2 is
quot;08.00-20.00quot; 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: www.excelforum.com/member.php...oamp;userid=26337
View this thread: 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: www.excelforum.com/member.php...oamp;userid=19982
View this thread: 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: www.excelforum.com/member.php...oamp;userid=26337
View this thread: 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),quot;.quot;,quot;:quot;)-SUBSTITUTE(LEFT(A1,5),quot;.quot;,quot;:quot;))*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 amp; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501797
I can easily change the format to be quot;09:00-18:00quot;, what would the
formula be then or is that it --
BankC
------------------------------------------------------------------------
BankC's Profile: www.excelforum.com/member.php...oamp;userid=26337
View this thread: 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:G1lt;gt;quot;quot;,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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501797
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 quot;54:00quot; (hours worked) turns to quot;-141.50quot; Any ideas? Also
is there anyway the formula can allow for text in a cell ie quot;DAY OFFquot;
with returning a #value error?

Cheers!

BankC--
BankC
------------------------------------------------------------------------
BankC's Profile: www.excelforum.com/member.php...oamp;userid=26337
View this thread: www.excelforum.com/showthread...hreadid=501797
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 quot;night shiftsquot;, 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)gt;RIGHT(A1:G1,5))))*24--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501797
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: www.excelforum.com/member.php...oamp;userid=26337
View this thread: www.excelforum.com/showthread...hreadid=501797
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: www.excelforum.com/member.php...oamp;userid=31006
View this thread: www.excelforum.com/showthread...hreadid=501797