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.

My question is:

Is it possible to format a cell so it displays a number as time format?

For example, I want type 1445 and I would like it to be displayed as 14:45 in time format.

The importat thing here is the "time format" part. I can format the cell with ##":"##, but this doesn't allow me to use the value like the time format.

share|improve this question
    
The time is in decimal notation. For you to use 1445 as a time you need to multiply 14 * 60 and add 45. Take the result and divide by 1440. –  wbeard52 Aug 21 '13 at 3:21
    
You can use VBA to check if the value is greater than one and if so, change it to the time format. If the value is less than one leave the value alone. –  wbeard52 Aug 21 '13 at 3:29

1 Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Column = 1 Then  'Column A
    If Target.Value > 1 Then 'convert the value to a time format
       If Len(Target.Value) > 2 Then   'only entered the hour... no minutes
         Target.Value = (Left(Target.Value, Len(Target.Value) - 2) * 60 + Right(Target.Value, 2)) / 1440
         Target.NumberFormat = "hh:mm"
       Else
         Target.Value = (Target.Value * 60) / 1440
       End If
    End If
 End If

End Sub

Place this code in the sheet you want to do this with. By default the code only checks Column A but you should be able to quickly change the column or row you want to check and perform this calculation on.

share|improve this answer

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.