[sorab]

I'm still stuck. Any advise pls.. I need my cell to be formatted to
show a date and time in 24hr format eg it should show me 1-may-09
24:00 , but I keep getting it as 2-may-09 00:00 . I know its the
same but I do want it to show me as 24:00.... is this possible in
excel. thks in advance.

[Peter Andrews]

"sorab" wrote in message
...
I'm still stuck. Any advise pls.. I need my cell to be formatted to
show a date and time in 24hr format eg it should show me 1-may-09
24:00 , but I keep getting it as 2-may-09 00:00 . I know its the
same but I do want it to show me as 24:00.... is this possible in
excel. thks in advance.

AIUI 1st May 2009 24:00 is 2nd May 2009 00:00, I don't believe that there is
such a time as 24:00! But no doubt someone with better knowledge will
correct me.

Peter

[Bernard Liengme]

Not that I can think of
best wishes
--
Bernard Liengme
MVP Excel
http://people.stfx.ca

"sorab" wrote in message
...
I'm still stuck. Any advise pls.. I need my cell to be formatted to
show a date and time in 24hr format eg it should show me 1-may-09
24:00 , but I keep getting it as 2-may-09 00:00 . I know its the
same but I do want it to show me as 24:00.... is this possible in
excel. thks in advance.

[joeu2004]

"Peter Andrews" wrote:
I don't believe that there is such a time as 24:00!

I believe that http://en.wikipedia.org/wiki/24-hour_clock is correct on this
point. In a nutshell, 24:00 is used and useful.


---- original message -----

"Peter Andrews" wrote in message
...

"sorab" wrote in message
...
I'm still stuck. Any advise pls.. I need my cell to be formatted to
show a date and time in 24hr format eg it should show me 1-may-09
24:00 , but I keep getting it as 2-may-09 00:00 . I know its the
same but I do want it to show me as 24:00.... is this possible in
excel. thks in advance.

AIUI 1st May 2009 24:00 is 2nd May 2009 00:00, I don't believe that there
is such a time as 24:00! But no doubt someone with better knowledge will
correct me.

Peter

[Rick Rothstein]

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(xlCellTypeFormul as)
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

--
Rick (MVP - Excel)


"sorab" wrote in message
...
I'm still stuck. Any advise pls.. I need my cell to be formatted to
show a date and time in 24hr format eg it should show me 1-may-09
24:00 , but I keep getting it as 2-may-09 00:00 . I know its the
same but I do want it to show me as 24:00.... is this possible in
excel. thks in advance.