Adding hours scheduled for week


W

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

Advertisements

F

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

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
 
J

Jacob Skaria

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

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


Jacob Skaria said:
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 said:
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!
 
W

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

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

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


Jacob Skaria said:
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 said:
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!
 
W

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

Fred Smith said:
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 said:
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!

.
 
Ad

Advertisements

J

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(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 said:
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 said:
Forgot to mention to format the formula cell to [h]:mm

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


Jacob Skaria said:
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!
 
W

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

Jacob Skaria said:
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 said:
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 said:
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!
 
J

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


Wet Nose said:
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 said:
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 said:
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!
 
Ad

Advertisements

W

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

Jacob Skaria said:
--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 said:
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 said:
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


:

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top