Adding hours scheduled for week

  • Thread starter Thread starter Wet Nose
  • Start date Start date
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!
 
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.
 
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
 
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!
Click to expand...
Click to expand...
 
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!
Click to expand...
Click to expand...
Click to expand...
 
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!
Click to expand...

.
Click to expand...
 
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!
Click to expand...
Click to expand...
Click to expand...
 
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!
Click to expand...
Click to expand...
Click to expand...
 
--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!
Click to expand...
Click to expand...
Click to expand...
 
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top