G
gerry.lisa
I would like Sunday, October 01, 2006
to show up as Sun Oct 1st
Thanks for your help!
....Lisa...Excel 2003
to show up as Sun Oct 1st
Thanks for your help!
....Lisa...Excel 2003
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
Excel dates are days since Jan 00 1900 so one day is 1 etc. If you type in
(assuming you are using US date formats) 10/01/06 in a cell, then use custom
format ddd mmm d it will return Sun Oct 1, you can't format for ordinals
like 1st, 2nd 3rd etc, you would need either an event macro or a formula in
another cell and neither is really a good solution. You can of course type
in Sun Oct 1st but it will be text
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)
(assuming you are using US date formats) 10/01/06 in a cell, then use custom
format ddd mmm d it will return Sun Oct 1, you can't format for ordinals
like 1st, 2nd 3rd etc, you would need either an event macro or a formula in
another cell and neither is really a good solution. You can of course type
in Sun Oct 1st but it will be text
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)
G
gerry.lisa
Thanks Peo!!
I didn't realize "d" would give me the numerical date, I thought the
"d's" were just for the days of the week. I think I can manage without
the ordinals.
....Lisa
I didn't realize "d" would give me the numerical date, I thought the
"d's" were just for the days of the week. I think I can manage without
the ordinals.
....Lisa
B
Barbara Wiseman
Lisa,
If you do want the ordinals you will probably have to use another cell.
This explains how it can be done.
http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal
Barbara
If you do want the ordinals you will probably have to use another cell.
This explains how it can be done.
http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal
Barbara
G
Gord Dibben
Good find Barbara.
Following the thread to Ron Rosenfield's event code gives another choice of
adding the ordinals without using an extra column.
Gord Dibben MS Excel MVP
Following the thread to Ron Rosenfield's event code gives another choice of
adding the ordinals without using an extra column.
Gord Dibben MS Excel MVP
G
gerry.lisa
Barbara said:Lisa,
If you do want the ordinals you will probably have to use another cell.
This explains how it can be done.
http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal
Barbara
Thanks Barbara for the link, I don't even know where/how to enter all
that code.
(I used to know Excel somewhat but it's scary how much I've forgotten
in 3 years)
....Lisa
B
Barbara Wiseman
Gordon,
Thank you, I have found the code on a different newsgroup thread.
I do not use macros much, but managed to get it to work.
Lisa,
As I had managed to get it to work I thought I would put the exact details
of how to do it, much thanks to Ron Rosenfeld.
Right click on the worksheet tab.
Select View Code from the dropdown menu.
Paste the code below into the window that opens.
=============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range, c As Range
Dim Suffix As String
Set aoi = [a1:a100] 'set this to where you might be entering dates
On Error GoTo Handler
If Not Intersect(Target, aoi) Is Nothing Or _
Not Intersect(Target.Dependents, aoi) Is Nothing Then
For Each c In aoi
If IsDate(c.Value) Then
Select Case Day(c.Value)
Case Is = 1, 21, 31
Suffix = "\s\t"
Case Is = 2, 22
Suffix = "\n\d"
Case Is = 3, 23
Suffix = "\r\d"
Case Else
Suffix = "\t\h"
End Select
c.NumberFormat = "mmm d" & Suffix & " yyyy"
End If
Next c
End If
Exit Sub
Handler: If Not Intersect(Target, aoi) Is Nothing Then Resume Next
If Err.Description <> "No cells were found." Then
MsgBox ("Error #" & Err & " " & Err.Description)
End If
End Sub
=============================
Now if you enter a date (or just edit it) it should appear with the ordinal
As I am in the UK I changed the Number format line to
c.NumberFormat = "ddd d" & Suffix & " mmm" & " yyyy"
Which gave me
Mon 28th Aug 2006
The original code gives
Aug 28th 2006
Good Luck, Barbara.
Thank you, I have found the code on a different newsgroup thread.
I do not use macros much, but managed to get it to work.
Lisa,
As I had managed to get it to work I thought I would put the exact details
of how to do it, much thanks to Ron Rosenfeld.
Right click on the worksheet tab.
Select View Code from the dropdown menu.
Paste the code below into the window that opens.
=============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range, c As Range
Dim Suffix As String
Set aoi = [a1:a100] 'set this to where you might be entering dates
On Error GoTo Handler
If Not Intersect(Target, aoi) Is Nothing Or _
Not Intersect(Target.Dependents, aoi) Is Nothing Then
For Each c In aoi
If IsDate(c.Value) Then
Select Case Day(c.Value)
Case Is = 1, 21, 31
Suffix = "\s\t"
Case Is = 2, 22
Suffix = "\n\d"
Case Is = 3, 23
Suffix = "\r\d"
Case Else
Suffix = "\t\h"
End Select
c.NumberFormat = "mmm d" & Suffix & " yyyy"
End If
Next c
End If
Exit Sub
Handler: If Not Intersect(Target, aoi) Is Nothing Then Resume Next
If Err.Description <> "No cells were found." Then
MsgBox ("Error #" & Err & " " & Err.Description)
End If
End Sub
=============================
Now if you enter a date (or just edit it) it should appear with the ordinal
As I am in the UK I changed the Number format line to
c.NumberFormat = "ddd d" & Suffix & " mmm" & " yyyy"
Which gave me
Mon 28th Aug 2006
The original code gives
Aug 28th 2006
Good Luck, Barbara.