Adding hours scheduled for week

[Wet Nose]

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 Smith]

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.

 

[Jacob Skaria]

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]

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

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

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

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!

 

[Wet Nose]

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

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

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

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

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!

 

[Wet Nose]

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

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.

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 Skaria]

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(C55),2)=0,C55)-
IF(MOD(COLUMN(C55),2)=1,C55))

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

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

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

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

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


:

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!

 

[Wet Nose]

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

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(C55),2)=0,C55)-
IF(MOD(COLUMN(C55),2)=1,C55))

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

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

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

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


:

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


:

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 Skaria]

--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

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

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(C55),2)=0,C55)-
IF(MOD(COLUMN(C55),2)=1,C55))

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

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

:

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

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


:

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


:

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!

 

[Wet Nose]

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

--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

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

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(C55),2)=0,C55)-
IF(MOD(COLUMN(C55),2)=1,C55))

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


:

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

:

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

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


:

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


:

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!