1. ## Formula not adding up (2003 sp2)

=(C23-B23)+(E23-D23)+(G23-F23)+(I23-H23)+(K23-J23)+(M23-L23)+(O23-N23)

Each pair of brackets represent hours worked on different days of the week Monday - Sunday, all cells are formatted as hh:mm in this example the shift is a Monday to Friday shift starting at 04:00 until 13:00 L23,M23,N23 and O23 are 00:00, I wuold expect the above formula to return 45 (C23[13:00]-B23[04:00])=9 etc. but the result I keep getting is 21

Thanks

Steve

2. ## Re: Formula not adding up (2003 sp2)

Hi Steve:

The cell that you have your formula in needs to be formatted as [hh]. Otherwise, when adding hours, when the total reaches 24 Excel resets the total to 0 as 24 hours is a full day. With the [hh] format, it keeps a full total of hours.

The difference you are getting (from what you expect) is 45-24=21

Regards,

3. ## Re: Formula not adding up (2003 sp2)

Thanks Tony

That did the trick for 5 of my shifts but for 3 of them 20:00 - 05:00, 22:00 - 07:00 and 00:00 - 09:00 I get ####### I am guesing that its to do with the negative time thing in Excel

Cheers

Steve

4. ## Re: Formula not adding up (2003 sp2)

If the end of a shift can be past midnight, you should either store the date+time of the start and end of a shift, or adjust the formula. For example, with start in B23 and end in C23, the formula

=C23-B23+(C23<B23)

will correctly return the duration even if the shift crosses midnight.

00:00 - 09:00 should not cause a problem unless 00:00 is actually 23:59:59 or so.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•