Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 0 down vote accepted
  • 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
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.