0

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!

0

1 Answer 1

0
  • 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
2
  • For rounding to nearest tenth, just set the format to only 1 decimal place
    – mcalex
    Dec 28, 2012 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, 2012 at 10:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .