Convert time cell into a decimal cell leaving values exact.

[jiayinggw2]

Here's my excel example

Date
1-Jan Thu
In
4:12 PM
Out
7:03 PM
In
7:20 PM
Out
12:03 AM
Exceptions
Regular
7:51
Lunch
0:17
Total
7:34

My solution I'm looking for is taking cell J4 "7:34" and making it show up as 7.34 in cell K4.

or alternatively, since I'm working on a payroll time sheet. I would like for some way, formula or otherwise to give me an absolute accurate value of 7 hours and 34 minutes, rolling over every 60 minutes to the next hour, any additional time that's added on properly when the "Total" cells are summed up for the total hours worked.

Right now what I'm getting is a percentage based on the overall hours for that period of time; using formula "=(J14-INT(J14))*24" in cell K4; which gives me 7.57 for the value in cell K4.

The problem with this is when you're dealing with time and money, this method overcompensates by .31 hour/minutes, making the dollar value based on the "excel interpretation of calculating the percentage of minutes of an hour" time, inaccurate.

The actual value of hours worked would be, for example, 84 hours and 46 minutes.

The excel summed up time with the formula above, equals 84.77 and the value I want in another cell that totals all the hours up should equal exactly 84.46.

Any ideas? thanks in advance.

[daddylonglegs]

I'm not sure I agree with your assessment. 84 hrs 46 mins as a decimal is correct as 84.77 (rounding to 2 decimal places).

If the hourly rate is $10 for example then using 84.77 (or more accurately to 3 decimal places 84.767) you just multiply the hourly rate by the decimal hours, and you get

=10*84.767 = $847.67

That's the correct amount, isn't it? If you use 84.46 then the total would be $844.60 which is incorrect.

That's the standard method for calculating amount to be paid

Amount to be paid = hourly rate * (hours as a time value) * 24

[jiayinggw2]

ok, nevermind. I guess I just confused myself with a problem that actually wasn't a problem. I think the conversion from hours to decimal kind of threw me off, for some reason the numbers didn't seem right. I went through a second time, making sure all the times were input correctly and even added up all the minutes myself and divided that by 60 just as a triple check and it came out 84.76 repeating, rounded up to 84.77.

Everything is fine then, thanks for the second set of eyes.