time sheet day total reads 8.78 instead of 9.18. The start time was 8:13 AM and end time was 5:00 PM. Lunch time is paid therefore the time out for lunch and the time back is the same. The total is 8:78 but that should read as 9 hrs and 18 minutes. How do I fix that?

Seems right to me. 5:00P -> 17.00 8:13A -> - 8.22 ──────── 8.78 -> 8 hrs 47 minutes

No, it's not. It should round up the 8 hrs to 9 and the minutes in excess of 60 (18) should be displayed instead of the 78

0.78 of an hour is not 1 hour 18 minutes. It's slightly more than three fourths of an hour. If you multiply 0.78 by the number of minutes in an hour (60), you get 47 minutes.

Whatever, if I were really good at Math, I wouldn't need Excel! Anyway, why won't it display as 9 hrs and 47Mins?

why won't it display as 9 hrs and 47Mins?Because it's only 8 hours and 47 Minutes:

From 8:13 to 9:13 equals 1st hour From 9:13 to 10:13 equals 2nd hour From 10:13 to 11:13 equals 3rd hour From 11:13 to 12:13 equals 4th hour From 12:13 to 13:13 equals 5th hour From 13:13 to 14:13 equals 6th hour From 14:13 to 15:13 equals 7th hour From 15:13 to 16:13 equals 8th hour From 16:13 to 17:00 equals 47 MinutesYou don't start counting at 8, you start at 9.

MIKE

OK, I am convinced of the math. But the displayed value is 8.78. The other totals for days where the start time was 8:30 or later displayed as 8:50.and an even 8:00. If you were the boss, how many hrs would you pay the employee for?

If you are going to be using Date & Time in Excel, it would be wise to learn how

Excel actually calculates them.Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a

fractional portionof a 24 hour day in the form: ddddd.tttttt .This is called a serial date, or serial date-time.

So the current Date & Time is: 6/14/2012 16:34

or as Excel sees it: 41074.69028See here for more complete info:

http://www.cpearson.com/excel/datet...

MIKE

That's probably way over my and my bosses level of interest in tech knowledge. I did something easier. I inserted another row and manually inserted the totals in what I am calling "Clock time" as follows: 8:30 8:47 and 8:00 and totalled across and got the same week total as the spread sheet yielded 25.28. I think that should convice the boss that the spreadsheet isn't faulty. Thanks for the input.

You could probably manually configure the cells to show a time. Depending on the version of Excel in question, this might happen automatically, but I guess your version doesn't.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History