Custom date format for days ... again ... thx <g>

[StargateFan]

Sorry about this but a recent question re custom date format was a
special case type of scenario and I'm finding that I still don't know
how to change to my preferred date format for most spreadsheets.
The new sheet I'm trying to fix might show how to do this once and for
all. In today's sheet I have 2 cells, B2 is formatted as "ddd" and C2
as "mmm.dd.yyyy". C2 is a date entered by user where B2 is dependent
on C2 via the formula "=C3". I was hoping to finally learn how to get
XL to change the "ddd" format of"
Sun, Mon, Tue, Wed, Thu, Fri Sat

to:
Sn, Mn, Tu, Wd, Th, Fr, Sa.

Thank you. D

 

[StargateFan]

On Wed, 11 May 2011 11:27:24 -0400, StargateFan

[snip]

all. In today's sheet I have 2 cells, B2 is formatted as "ddd" and C2
as "mmm.dd.yyyy". C2 is a date entered by user where B2 is dependent
on C2 via the formula "=C3". I was hoping to finally learn how to get

[snip]

Oops, sorry for typo. The formula on third line above should read"

'on C2 via the formula "=C2".'

D

 

[Jim Cone]

=CHOOSE(WEEKDAY(C2),"Sn","Mn","Tu","Wd","Th","Fr","Sa")

--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Formats & Styles xl add-in: lists/removes unused styles/number formats - in the free folder)




"StargateFan" <IDon'(e-mail address removed)>
wrote in message
news:[email protected]...

 

[StargateFan]

=CHOOSE(WEEKDAY(C2),"Sn","Mn","Tu","Wd","Th","Fr","Sa")

Awesome, that works beautifully!

But dunce that I am, I couldn't change this when testing the syntax,
to see how it might work in formulas such as ddd.mm.dd.yyyy where ddd
would replace the Sun, Mon ... to Sn, Mn. I see that this formula
changes the format regardless of what the format of the cell itself is
(kewl, that). So even when I changed the "ddd" format to
"ddd.mm.dd.yyyy", the day still remained as Sn, Mn, Tu ... etc.,
instead.

Last question, what if I needed ddd.mm.dd.yyyy so that the ddd part
comes out as Sn, Mn, Tu? I think that'll cover all cases in the
future I might need.

Thanks so much. I should have tracked down how to do this years ago!
<g> Well, at least I'm getting this down now. Thanks and cheers!!

D

 

[Gord Dibben]

That's because the data is now Text so cannot be "reformatted".


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

But dunce that I am, I couldn't change this when testing the syntax,
to see how it might work in formulas such as ddd.mm.dd.yyyy where ddd
would replace the Sun, Mon ... to Sn, Mn.

Hmmm. Your question is not clear, especially since ddd.mm.dd.yyyy is NOT a formula.

Perhaps this is what you are asking for?:

=TEXT(C2,"""" &CHOOSE(WEEKDAY(C2),"Sn","Mn","Tu","Wd","Th","Fr","Sa")&"."""&"mm.dd.yyyy")

That will display the result as a TEXT STRING looking like: Th.05.12.2011

But note that this result is a text string, and Excel will not be able to interpret this directly as a date.

If you really want to change the FORMAT, so as to see the date in this format, you will need to use VBA -- probably with event code. This would give you 7 custom formats. It would allow you to use the results in subsequent date calculations.

For example:

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

Be sure to read the notes carefully, with regard to the range you might wish to specially format and other considerations of using this method:

=====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeToCheck As Range, c As Range
Dim sWeekday As Variant

sWeekday = Array("", "Sn", "Mn", "Tu", "Wd", "Th", "Fr", "Sa")

'Assume Column B is the range to format
'since your Column B relevant cells will all contain the formula:
' =Cn, then the below line will work.
'Otherwise, you may need a different algorithm to select the
' appropriate cells to format

On Error Resume Next 'in case the SpecialCells selection fails
If Not Range("B:B").SpecialCells(xlCellTypeFormulas, xlNumbers) Is Nothing Then
For Each c In Range("B:B").SpecialCells(xlCellTypeFormulas, xlNumbers)
c.NumberFormat = """" & sWeekday(Weekday(c.Value)) & ".""" & "mm.dd.yyyy"
Next c
End If

'need to reset format if we don't want the special date format
If Not Range("B:B").SpecialCells(xlCellTypeConstants, xlNumbers) Is Nothing Then
For Each c In Range("B:B").SpecialCells(xlCellTypeConstants, xlNumbers)
c.NumberFormat = "General"
Next c

End If

End Sub
=========================================

 

[Ron Rosenfeld]

Unneccessary code removed:

=============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeToCheck As Range, c As Range
Dim sWeekday As Variant

sWeekday = Array("", "Sn", "Mn", "Tu", "Wd", "Th", "Fr", "Sa")

'Assume Column B is the range to format
'since your Column B relevant cells will all contain the formula:
' =Cn, then the below line will work.
'Otherwise, you may need a different algorithm to select the
' appropriate cells to format

On Error Resume Next 'in case the SpecialCells selection fails

For Each c In Range("B:B").SpecialCells(xlCellTypeFormulas, xlNumbers)
c.NumberFormat = """" & sWeekday(Weekday(c.Value)) & ".""" & "mm.dd.yyyy"
Next c

'need to reset format if we don't want the special date format
For Each c In Range("B:B").SpecialCells(xlCellTypeConstants, xlNumbers)
c.NumberFormat = "General"
Next c

End Sub
==============================

 

[StargateFan]

That's because the data is now Text so cannot be "reformatted".

Interesting, that's good to know. Didn't realize that was why. No
worries, just nice to have what we wonder about answered <g>.

Thank you!

 

[StargateFan]

Wow, this is all very interesting. Yes, I have had that problem in
the past. The data gets converted, to text as you say, and then
formulas don't really work. But since the end result gave me what I
needed, no worries. As long as end result was what was needed, I've
lived with the "limitation". But this macro might work around that.
I'm constantly amazed by the potential Excel has of allowing for us to
get our cake _and_ eat it, too! I'll definitely give this a try.

 

[StargateFan]

Unneccessary code removed:

<vbg> Whittled it down, I take it! Awesome!

You guys are the best. Like always, when anyone marvels at my work
results, I absolutely give credit where credit is due and I tell
colleagues that it's you MVPs at the MS ngs that make us look good.
Believe me when I say that I never take the credit for myself! But
there is no question that we keep up with the demands of work due to
these types of solutions.

Thanks! D

 

[Ron Rosenfeld]

<vbg> Whittled it down, I take it! Awesome!

You guys are the best. Like always, when anyone marvels at my work
results, I absolutely give credit where credit is due and I tell
colleagues that it's you MVPs at the MS ngs that make us look good.
Believe me when I say that I never take the credit for myself! But
there is no question that we keep up with the demands of work due to
these types of solutions.

Thanks! D

Glad to help. Thanks for the feedback