I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

It would be useful if you showed us the formula you were using.

Excel treats times as numbers, so all arithmetic operations work on them,
including Sum.

Regards,
Fred.

"Wet Nose" <Wet wrote in message
...
I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time
in)
and it will work for one day, but I can't figure out how to add the
remaining
work days - all it get is errors. Please help!

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

Jacob,

My employee's times actually go horizontal so start times would be cells
C5,E5,G5,I5,K5,M5,O5, and end times would be cells D5,F5,H5,J5,L5,N5,P5 and
the sum cell would be Q5. Thanks for your help :)

Sheila

"Jacob Skaria" wrote:

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

Fred,

To be honest, I'm very "green" when it comes to excel formulas. I'm a kind
of "learn as I go" kind of girl. So I really don't have anything I'm working
with, just trying different variations of what I've googled. Nothing's
worked so far, hence why I'm here looking for expertise.

Sheila

"Fred Smith" wrote:

It would be useful if you showed us the formula you were using.

Excel treats times as numbers, so all arithmetic operations work on them,
including Sum.

Regards,
Fred.

"Wet Nose" <Wet wrote in message
...
I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time
in)
and it will work for one day, but I can't figure out how to add the
remaining
work days - all it get is errors. Please help!

.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUMPRODUCT(IF(MOD(COLUMN(C5:P5),2)=0,C5:P5)-
IF(MOD(COLUMN(C5:P5),2)=1,C5:P5))

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

Jacob,

My employee's times actually go horizontal so start times would be cells
C5,E5,G5,I5,K5,M5,O5, and end times would be cells D5,F5,H5,J5,L5,N5,P5 and
the sum cell would be Q5. Thanks for your help :)

Sheila

"Jacob Skaria" wrote:

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

I entered the formula exactly like you suggested and I got an "value error".
The formula cell is formatted [h]:mm and the data cells are formatted hh;mm
AM/PM. Any suggestions? Thanks so much.

Sheila

"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUMPRODUCT(IF(MOD(COLUMN(C5:P5),2)=0,C5:P5)-
IF(MOD(COLUMN(C5:P5),2)=1,C5:P5))

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

Jacob,

My employee's times actually go horizontal so start times would be cells
C5,E5,G5,I5,K5,M5,O5, and end times would be cells D5,F5,H5,J5,L5,N5,P5 and
the sum cell would be Q5. Thanks for your help :)

Sheila

"Jacob Skaria" wrote:

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

--Do you have an text values in between
--Did you try the formula in a fresh worksheet

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I entered the formula exactly like you suggested and I got an "value error".
The formula cell is formatted [h]:mm and the data cells are formatted hh;mm
AM/PM. Any suggestions? Thanks so much.

Sheila

"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUMPRODUCT(IF(MOD(COLUMN(C5:P5),2)=0,C5:P5)-
IF(MOD(COLUMN(C5:P5),2)=1,C5:P5))

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

Jacob,

My employee's times actually go horizontal so start times would be cells
C5,E5,G5,I5,K5,M5,O5, and end times would be cells D5,F5,H5,J5,L5,N5,P5 and
the sum cell would be Q5. Thanks for your help :)

Sheila

"Jacob Skaria" wrote:

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!

Jacob,

I don't have text values in between. I did paste the formula in a fresh
worksheet and it worked! So are you thinking that I have to re-create this
worksheet from scratch? I suppose copy and paste won't work in this
instance? Thanks, Sheila

"Jacob Skaria" wrote:

--Do you have an text values in between
--Did you try the formula in a fresh worksheet

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I entered the formula exactly like you suggested and I got an "value error".
The formula cell is formatted [h]:mm and the data cells are formatted hh;mm
AM/PM. Any suggestions? Thanks so much.

Sheila

"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=SUMPRODUCT(IF(MOD(COLUMN(C5:P5),2)=0,C5:P5)-
IF(MOD(COLUMN(C5:P5),2)=1,C5:P5))

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

Jacob,

My employee's times actually go horizontal so start times would be cells
C5,E5,G5,I5,K5,M5,O5, and end times would be cells D5,F5,H5,J5,L5,N5,P5 and
the sum cell would be Q5. Thanks for your help :)

Sheila

"Jacob Skaria" wrote:

Forgot to mention to format the formula cell to [h]:mm

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With data arranged as below try the formula

Col A Col B
9:30 AM 3:30 PM
10:30 AM 3:30 PM
8:30 AM 3:30 PM
8:30 AM 3:30 PM
9:00 AM 3:30 PM

=SUMPRODUCT(B1:B5-A1:A5)

If this post helps click Yes
---------------
Jacob Skaria


"Wet Nose" wrote:

I have created a work schedule with start/end time cells formated hh;mm
AM/PM. The "total hours scheduled" cell is formated [h]:mm. I need a
formula for the "total" cell that will add the weekly shifts. Ex; Monday
10-5 + Tuesday 12-8 etc. equals 15:00 I've tried to SUM (time out-time in)
and it will work for one day, but I can't figure out how to add the remaining
work days - all it get is errors. Please help!