Format a cell so that if a 7 is entered it is shown as 07:00:00

[Peo Sjoblom]

Please post the question in the body of the message, do not post the
question in the subject line only

This is not possible using formatting

 

[JE McGimpsey]

Not possible using formatting.

One workaround would be to use this event macro (right-click the
worksheet tab and choose view code):



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("B3")) Is Nothing Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = .Value / 24
Application.EnableEvents = True
On Error GoTo 0
.NumberFormat = "[hh]:mm:ss"
End If
End If
End With
End Sub

Change the cell address to suit.

Gregory OUSTED <[email protected]> wrote:

 

[Harlan Grove]

This is not possible using formatting

No?

A1: 7

A1's number format: 00":00:00"

Yes, this is almost certainly not what the OP wants, but it *IS* possible to
*SHOW* 07:00:00 when 7 is entered. It'll just be 7, not 7 o'clock.