Change dates to a custom format via formula ... how to?

[StargateFan]

Hello,

A2 has formula =NOW()
which makes date today in this format:
Tue.Apr.26.2011

How can I get my custom date formats so that the above date shows up
as Tu.Apr.26.2011.

In another sheet, I was kindly given this to make these types of
changes:
=IF($A$2<>"",TEXT($A$2,"yymmdd.")&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

I tried this,
=NOW()&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"), but
that didn't work <g>, not that I thought it would. But was wondering
how I can do this for this workbook and others in future.

Thanx! D

 

[Ron Rosenfeld]

Hello,

A2 has formula =NOW()
which makes date today in this format:
Tue.Apr.26.2011

How can I get my custom date formats so that the above date shows up
as Tu.Apr.26.2011.

In another sheet, I was kindly given this to make these types of
changes:
=IF($A$2<>"",TEXT($A$2,"yymmdd.")&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"")

I tried this,
=NOW()&CHOOSE(WEEKDAY($A$2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"), but
that didn't work <g>, not that I thought it would. But was wondering
how I can do this for this workbook and others in future.

Thanx! D

If you always want to print out today's date:

=CHOOSE(WEEKDAY(TODAY()),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(TODAY(),"\.mmm.dd.yyyy")

Note that I use TODAY() instead of NOW() as NOW() also includes the time of day, which is unneccessary in this formula.

If you want to print out the data that is in A2, then merely substitute A2 for TODAY() in the above formula:

=CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy")

And if you want to print a blank if A2 is empty, nest the above into an IF statement:

=IF(A2="","",CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"))

or, better to test if there is a number in A2 since dates are stored as numbers. This would also print a blank, instead of an error, if A2 is text or error.

=IF(ISNUMBER(A2),CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"),"")

 

[Rick Rothstein]

A2 has formula =NOW()

which makes date today in this format:
Tue.Apr.26.2011

How can I get my custom date formats so that the above date
shows up as Tu.Apr.26.2011.

Here is a formula for you to try...

=MID("SnMnTuWdThFrSa",2*WEEKDAY(TODAY())-1,2)&TEXT(TODAY(), "\.mmm.dd.yyyy")

Rick Rothstein (MVP - Excel)

 

[StargateFan]

If you always want to print out today's date:

=CHOOSE(WEEKDAY(TODAY()),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(TODAY(),"\.mmm.dd.yyyy")

Note that I use TODAY() instead of NOW() as NOW() also includes the time of day, which is unneccessary in this formula.

If you want to print out the data that is in A2, then merely substitute A2 for TODAY() in the above formula:

=CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy")

And if you want to print a blank if A2 is empty, nest the above into an IF statement:

=IF(A2="","",CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"))

or, better to test if there is a number in A2 since dates are stored as numbers. This would also print a blank, instead of an error, if A2 is text or error.

=IF(ISNUMBER(A2),CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa") & TEXT(A2,"\.mmm.dd.yyyy"),"")

Awesome, thank you for so many options. I use this short date format
all the time so it's neat to know how to display it now.

Thx.

 

[StargateFan]

Here is a formula for you to try...

=MID("SnMnTuWdThFrSa",2*WEEKDAY(TODAY())-1,2)&TEXT(TODAY(), "\.mmm.dd.yyyy")

Rick Rothstein (MVP - Excel)

Thank you! This worked wonderfully. I didn't which code to choose so
I created a copy of the worksheet and put your code in one and Ron
Rosenfeld's in the other. That gives me two options to fall back on
when I re-use this code again in future. In the meantime, this does
the current job just great. Thx.

 

[Ron Rosenfeld]

Awesome, thank you for so many options. I use this short date format
all the time so it's neat to know how to display it now.

Thx.

Glad to help. Thanks for the feedback.