Timesheet

Good Day

I am currently doing a timesheet using excel with Start Time / Lunch Taken / End Time. The issue I am having is calculating the difference in time working taking the lunch time into consideration.
I am getting a variance in the total hours worked against adding manually.

I have attached a spreadsheet for clarity. the yellow highlight is added manually.

Thanks

What you are doing manually is adding the hours and minutes separately, so you get 34 hours and 90 minutes. Of course, 90 minutes is the same as 1 hour and 30 minutes, giving 35:30, as you have in cell E18. Your manual total of 34.90, as if this is interpreted directly as fractional hours, this would be less than 35 hours, so you would be doing your employees out of a bit of working time.

Apply a custom format of:

[h]" Hrs. and "m" Mins."

to cell E18 if you want it to look like the other cells above. The square brackets around the h term prevents the hours from wrapping at 24.

Hope this helps.

Pete

Hi Pete

I formatted as per your suggestion, the total I am now getting is 11 hours and 30 min
Should I apply the formatting to all cells?

Regards

The format that you have applied is allowing the hours to wrap at 24 (i.e. it ignores any multiples of 24, as it sees these as equivalent to a day). You need to put square brackets around the h term in the format string, as in my post.

Pete