# Formatting Time Entries in Excel 2010

I have an Excel Spreadsheet. Essentially, I'm trying to use this spreadsheet for time tracking purposes. I have the following columns:

``````Date (Formatted as a Date in Column A)
Start Time (Formatted as a Time in Column B)
End Time (Formatted as a Time in Column C)
Duration (Formatted as a Time in Column D with a formula of C# - B#)
``````

The calculation of the duration cell looks to be correct. However, I want to format it in decimal format rounded to the nearest tenth. For instance, if my Start Time value is 10:00 AM and my End Time value is 11:06 AM, I want the Duration to show 1.1.

How do I format a Time value as a Decimal rounded to the nearest tenth?

Thank you!

-

• Change the format of column D to general
• Insert this formula to column D: `=(C1-B1)*24`

The trick is to multiply it with 24

The harder part is to round it to the nearest tenth. I only know a solution using VBA.
This is an example for rounding to any quarter of an hour

``````Private Function RoundMyTime(mytime As Date) As Date
If Minute(mytime ) Mod 15 < 8 Then
RoundMyTime= DateAdd("n", -Minute(mytime ) Mod 15, mytime )
Else
RoundMyTime= DateAdd("n", 15 - Minute(mytime ) Mod 15, mytime )
End If
End Function
``````
-
For rounding to nearest tenth, just set the format to only 1 decimal place – mcalex Dec 28 '12 at 4:44
Formating to 1 decimal place wont affect the real value. But thats important if you want to excel further or copy the values. Since this, I didn't recommend this solution :) – nixda Dec 28 '12 at 10:54