Total time calculating incorrectly

no data entered ...????

and in K14 why not ..

=SUM(D14:J14)

Thank you for the reply,

I have uploaded another version which is filled out to show an example. I did originally have that formula, but if I remember correctly, it was misbehaving, so that's why I did each cell separately.

Regards,

Dave

You need to format the Total cells as [h]:mm to cater for times over 24 hours

And change your heading ..."Total time calculating incorrectly" (or similar) see below ...

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

I have formatted the cells as time with the 4 digit 24hr hour format i.e. 13:30. Please see the attached image. Time.PNG

Regards,

Dave

See attached and format used in K14::

Thank you for your reply, sadly that doesn't work either, when putting in a night shift, it comes up with a total of 560:00.

Post a file showing the incorrect value.

See attached with Night shift: nothing wrong as far As I can see. Check J50.

I am not too sure if it is due to the original formula that I have used to calculate whether the shifts go past midnight or not i.e. =D9-D8+(D9<D8)*24

Please see the attached file.

You changed all cells rather than just those in Column K.

See file I posted.

I have even tried the file that you have posted, and in week one, it still displays 576:00 as as the grand total for an early, a late and a night shift. It appear to be the way that the cell F8 is calculating the night shift. After I have filled out 23:00 and when I show the formulas using CTRL ` the values are different between F8 and G30. I just can't see where it is going wrong.

I have resolved the strange issue by copy and pasting from the cell where the formulas work and pasting into the place where they don't.

I would like to say thank you so much for all of your help this evening.

Regards,

Dave

I thought it was solved, but sadly it doesn't seem to be working again. I think that it is the way that it is formatting the the value that I am entering or perhaps it is the way that I am entering the value, I am literally typing 23:00 I am not too sure if this is the issue?

Your formula should be ..

=D9-D8+(D9<D8)*1 (not 24)

That's amazing, it seems to have resolved the issue.

Once again, I would like to thank you for all of your help, it has been years since I have used Excel in-depth like this and most of it came from Google.

Regards,

Dave

that 560 value in col F is caused by adding 24 days to the value.

change the formula to

Formula:

Please Login or Register  to view this content.

and it will give you 8 hours as it is supposed to.

Times are a fraction of 1 1 hour is in decimalvalue 0,04166666667 24 hours in decimal 1
when endtime is smaller then begin time your old formula caused a calucations of 1*24 = 24 is 24*24 hours = 576 hours - (23:00-7:00=16 hours) = 560 hours
combined with the formaterror it gives you strange results

You have been struggling with 2 errors (in format and in formula) that by chance displayed the expected value.

In your file I have made some calculations and formats to show you the effect. hop that will help you understand the challenges of calculating with time in excel.