Formatting dates

[raybus]

I am trying to format this date "January 4, 2021 11:30 am" so I can formulate the day of the week "Mon" but excel is not recognizing the format can you please assist

 

[6StringJazzer]

What does "excel is not recognizing the format" mean? What actually happens?

The cell should have 1/4/2021 11:30 AM as the time/date value shown in the formula box (or 4/1/2021 for non-US). When I put "January 4, 2021 11:30 am" into a cell that is what I get. Then you can format any way you want such as custom format "Ddd".

From your description it is unclear if you have a problem with your data, or if you are asking how to format a date as day-of-week.

 

[Toadstool]

Hi Raybus,

How Excel interprets dates depends on your region and the format of the date, text or number.

If you need to convert it for some reason you can try one of these:

Raybus.xlsx
	A	B	C
1	Date		Day of Week
2	1/4/2021 11:30		Mon
3	1/4/2021 11:30		Mon
4	January 4, 2021 11:30 am		Mon
5	January 4, 2021 11:30 am		Mon
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXT(A2,"ddd")
C3	C3	=TEXT(VALUE(A3),"ddd")
C4	C4	=TEXT(DATEVALUE(A4),"ddd")
C5	C5	=TEXT(DATE(MID(A5,SEARCH(" ",A5,SEARCH(" ",A5)+1)+1,4),MATCH(LEFT(A5,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),SUBSTITUTE(MID(A5,SEARCH(" ",A5)+1,2),",","")),"ddd")

 

[raybus]

What does "excel is not recognizing the format" mean? What actually happens?

The cell should have 1/4/2021 11:30 AM as the time/date value shown in the formula box (or 4/1/2021 for non-US). When I put "January 4, 2021 11:30 am" into a cell that is what I get. Then you can format any way you want such as custom format "Ddd".

From your description it is unclear if you have a problem with your data, or if you are asking how to format a date as day-of-week.

My apologies for not being clear enough yes i am asking how to format a date as a day of the week

thanks for your quick response

 

[6StringJazzer]

To format a date as day-of-week use Custom format "Ddd"

 

[Joe4]

Note that if you apply a format any date/time, it only changes the display value, not the actual underlying values used in calculations.

So, even if you format the cell as "ddd" to show "Mon", any calculations referencing the cell will still see the date/time, and not "Mon".

Just something you want to be aware of.