You cannot do what you want with a worksheet cell format, but there is a VB
solution if your worksheet's security level allows you to make use of VB
code. Right click the tab at the bottom of the worksheet that you want to
have this functionality and select View Code from the popup menu that
appears, then copy/paste the following code into the code window that
appears...
'*************** START OF CODE ***************
Const CellsToFormat As String = "C:C,F:F"
Sub SetNumberFormat0000to2400(Optional CellAddresses As String = "")
Dim R As Range
Dim C As Range
If Len(CellAddresses) = 0 Then
Set R = Range(CellsToFormat).SpecialCells(xlCellTypeFormulas)
Else
Set R = Intersect(Range(CellsToFormat), Range(CellAddresses))
End If
If R Is Nothing Then Exit Sub
For Each C In R
If C.Value = "" Then
C.NumberFormat = "General"
ElseIf IsDate(C.Value) And CDbl(C.Value) > 0 Then
If TimeValue(C.Value) = "12:00:00 AM" Then
If Int(C.Value) = 0 Then
C.NumberFormat = "24:00"
Else
C.NumberFormat = "d-mmm-yy ""24:00"""
End If
ElseIf Int(C.Value) = 0 Then
C.NumberFormat = "hh:mm"
Else
C.NumberFormat = "d-mmm-yy hh:mm"
End If
Else
C.NumberFormat = "d-mmm-yy hh:mm"
If C.Value = 0 Then C.NumberFormat = "d-mmm-yy ""24:00"""
End If
Next
End Sub
Private Sub Worksheet_Calculate()
SetNumberFormat0000to2400
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
SetNumberFormat0000to2400 Target.Address
End Sub
'*************** END OF CODE ***************
The only thing you need to do is change the range of cells I've assigned to
the CellsToFormat constant in the Const statement at the beginning of the
code to the range of cells to be covered by this "formatting" code (my
example sets the constant to Columns C and F). Now, this code will not
update constant values already in place in those columns, but it will handle
all future entries. To update all existing constants, just run this macro
one time (add it to the same code window you put the above code into and
then remove it after you have run it...
Sub Initialize0000to2400Format()
Dim C As Range
Dim R As Range
Set R = Intersect(ActiveSheet.UsedRange, Range(CellsToFormat))
For Each C In R
If Not C.HasFormula Then C.Value = C.Value
Next
End Sub
