Change date format layout from ddd.

Mitchell

New Member
Joined
Mar 20, 2007
Messages
37
Good afternoon!

After more than a couple of hours of googling and working on this, I have this formula:

=CHOOSE(WEEKDAY(TODAY()),"Sn","M","T","W","Th","F","Sa") & TEXT(TODAY(),"\.mmm-dd-yyyy")

which yields the correct format for the days for me like this (say today):

Th.Dec-10-2020

I need to switch the date format up in order to have items sort chronologically properly. So instead of, say, today's date of Th.Dec-10-2020, I need this:

2020-12-10.Th

How can this be done, please? If anyone can help, please advise. I've tried everything I know but I keep breaking the formula changing and moving the elements around so I know I'm missing something.

Thank you for any help in this regard!

(Oops, have incomplete title and can't see how to fix in editing. Title should read to change formatting from ddd.mmm.dd.yyyy to yyyy.mm.dd.ddd. Sorry!)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe =TEXT(TODAY(),"yyyy.mm.dd.ddd") 2020.12.11.Fri
my today() is 11/12/2020
or I misunderstood
 
Last edited:

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
Sandy has a good solution but you can never have too many answers. I tried this formula and it seems to work for me.

=TEXT(TODAY(),"yyyy-mm-dd")& " ." &CHOOSE(WEEKDAY(TODAY()),"Sn","M","T","W","Th","F","Sa")
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Assuming you have to have the day name abbreviations you showed, here is another formula that seems to work...

=TRIM(TEXT(TODAY(),"yyyy-mm-dd.")&MID(" SnM T W ThF Sa",2*WEEKDAY(TODAY()),2))
 

Mitchell

New Member
Joined
Mar 20, 2007
Messages
37
Sandy has a good solution but you can never have too many answers. I tried this formula and it seems to work for me.

=TEXT(TODAY(),"yyyy-mm-dd")& " ." &CHOOSE(WEEKDAY(TODAY()),"Sn","M","T","W","Th","F","Sa")

My apologies in the delay in responding. I didn't see/get an email notification so going to see what I'm doing wrong as you all answered so very quickly! Much appreciated. I could finish my workbook now.

This did the trick, that's awesome! But so did Rick's, too! So using Sandy's as it's a little bit shorter but keeping both snippets. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top