Hi. Can any one tell me the formula for adding up 3 different start and
finish times within a 24 hour period.

I have set up a time sheet that has shifts of Morning, Afternoon and Evening
i enter in my start and finish times for each shift but can not figure out
how to add them all together.
--
Thank you for reading my post. Hopefully you can answer my querie

Hi

With your data in A2:F2 enter in G2
=MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1)
Format G2 as hh:mm

The MOD part of the formula will deal with any shifts crossing the 24
hour barrier.

If you want your result in decimal hours, then you would need
=24*(MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1))
and format the cell to the number of decimals you require.
This is because Excel stores times as a fraction of a day (24 hours).
--
Regards
Roger Govier

Shazza wrote:
Hi. Can any one tell me the formula for adding up 3 different start and
finish times within a 24 hour period.

I have set up a time sheet that has shifts of Morning, Afternoon and Evening
i enter in my start and finish times for each shift but can not figure out
how to add them all together.

Hi Roger
thanks for your response. this one works if every cell is populated but on
quite a few occasions there will be nothing in some of the cells. An example
of what i have is as follows

Start Finish Start Finish Start Finish Total
05:00 12:00 17:00 00:00
09:00 13:00 14:00 17:00 18:00 11:00

So i am finding that on the days where no time has been entered then the
formula does not work.
--
Thank you for reading my post. Hopefully you can answer my querie


"Roger Govier" wrote:

Hi

With your data in A2:F2 enter in G2
=MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1)
Format G2 as hh:mm

The MOD part of the formula will deal with any shifts crossing the 24
hour barrier.

If you want your result in decimal hours, then you would need
=24*(MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1))
and format the cell to the number of decimals you require.
This is because Excel stores times as a fraction of a day (24 hours).
--
Regards
Roger Govier

Shazza wrote:
Hi. Can any one tell me the formula for adding up 3 different start and
finish times within a 24 hour period.

I have set up a time sheet that has shifts of Morning, Afternoon and Evening
i enter in my start and finish times for each shift but can not figure out
how to add them all together.
.

You'll need to explain what "does not work" means, because Roger's formula
worked for me when I tested it, including the sample where there was no
start/finish time.

Regards,
Fred

"Shazza" wrote in message
...
Hi Roger
thanks for your response. this one works if every cell is populated but
on
quite a few occasions there will be nothing in some of the cells. An
example
of what i have is as follows

Start Finish Start Finish Start Finish Total
05:00 12:00 17:00 00:00
09:00 13:00 14:00 17:00 18:00 11:00

So i am finding that on the days where no time has been entered then the
formula does not work.
--
Thank you for reading my post. Hopefully you can answer my querie


"Roger Govier" wrote:

Hi

With your data in A2:F2 enter in G2
=MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1)
Format G2 as hh:mm

The MOD part of the formula will deal with any shifts crossing the 24
hour barrier.

If you want your result in decimal hours, then you would need
=24*(MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1))
and format the cell to the number of decimals you require.
This is because Excel stores times as a fraction of a day (24 hours).
--
Regards
Roger Govier

Shazza wrote:
Hi. Can any one tell me the formula for adding up 3 different start
and
finish times within a 24 hour period.

I have set up a time sheet that has shifts of Morning, Afternoon and
Evening
i enter in my start and finish times for each shift but can not figure
out
how to add them all together.
.

Hi

then try

=if(count(A2:B2)=2,MOD(B2-A2,1),0)+if count(C2:D2)=2,
MOD(D2-C2,1),0)+if(count(E2:F2)=2,MOD(F2-E2,1),0)
--
Regards
Roger Govier

Roger Govier wrote:
Hi

With your data in A2:F2 enter in G2
=MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1)
Format G2 as hh:mm

The MOD part of the formula will deal with any shifts crossing the 24
hour barrier.

If you want your result in decimal hours, then you would need
=24*(MOD(B2-A2,1)+MOD(D2-C2,1)+MOD(F2-E2,1))
and format the cell to the number of decimals you require.
This is because Excel stores times as a fraction of a day (24 hours).
--
Regards
Roger Govier

Shazza wrote:
Hi. Can any one tell me the formula for adding up 3 different start
and finish times within a 24 hour period.

I have set up a time sheet that has shifts of Morning, Afternoon and
Evening
i enter in my start and finish times for each shift but can not figure
out how to add them all together.