Not getting the right result in timesheet

June 13, 2012 at 19:53:42
Specs: Windows 7
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?

See More: Not getting the right result in timesheet

Report •


#1
June 13, 2012 at 20:07:19
Seems right to me.
5:00P ->    17.00
8:13A ->  -  8.22
         ────────
             8.78 -> 8 hrs 47 minutes

How To Ask Questions The Smart Way


Report •

#2
June 14, 2012 at 06:26:19
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

Report •

#3
June 14, 2012 at 06:41:41
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.

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
June 14, 2012 at 06:50:46
Whatever, if I were really good at Math, I wouldn't need Excel! Anyway, why won't it display as 9 hrs and 47Mins?

Report •

#5
June 14, 2012 at 11:58:25
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 Minutes

You don't start counting at 8, you start at 9.

MIKE

http://www.skeptic.com/


Report •

#6
June 14, 2012 at 13:18:54
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?

Report •

#7
June 14, 2012 at 13:37:38
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 portion of 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.69028

See here for more complete info:

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

MIKE

http://www.skeptic.com/


Report •

#8
June 14, 2012 at 13:49:05
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.


Report •

#9
June 15, 2012 at 04:58:01
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.

How To Ask Questions The Smart Way


Report •


Ask Question