Need Day from the Date Time format Excel 365

[hollyanneu2]

For the love of god, 365 is driving me NUTS!

I have a column with a Date Time format and I added a column next to it to give me the day but nothing is working...

Here's what I done:

• =Text(E2,"dddd") >>> data validation error because of the "hidden time"
• the Custom does not have "dddd" listed
• =MONTH(E2) & "/" & DAY(E2) & "/" & YEAR(E2) to get rid of the time part, then copied and pasted the date as value in another column to get rid of the "hidden" time value and then tried =Text(E2,"dddd")
• I tried using the *Wednesday, March 14, 2012 format and then tried to separate the day from the date/time using the Text to Column and oddly I only see the Date/Time format in the Preview of selected data box....

I need the day in a separate column from the original Date/Time format. Any solution to that???

 

[Fluff]

Why are you using data validation, rather than a formula?

 

[MARK858]

the Custom does not have "dddd" listed

Then just type in dddd in the box where it says "Type" and click OK.

 

[Dave Patton]

What exactly to you require?
Possibly something in the following will help.
You can use custom formatting or a formula.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Fri 19-Feb-21 15:23	19	Fri	Friday
3
5c

Cell Formulas
Range	Formula
B2	B2	=DAY(A2)
C2	C2	=TEXT(A2,"ddd")
D2	D2	=TEXT(A2,"dddd")

 

[hollyanneu2]

Why are you using data validation, rather than a formula?

I wasn't. It kept firing that error for some odd reason. I tried clearing it... No avail.

 

[hollyanneu2]

What exactly to you require?
Possibly something in the following will help.
You can use custom formatting or a formula.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Fri 19-Feb-21 15:23	19	Fri	Friday
3
5c

Cell Formulas
Range	Formula
B2	B2	=DAY(A2)
C2	C2	=TEXT(A2,"ddd")
D2	D2	=TEXT(A2,"dddd")

What exactly to you require?
Possibly something in the following will help.
You can use custom formatting or a formula.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Fri 19-Feb-21 15:23	19	Fri	Friday
3
5c

Cell Formulas
Range	Formula
B2	B2	=DAY(A2)
C2	C2	=TEXT(A2,"ddd")
D2	D2	=TEXT(A2,"dddd")

I used the =text(cell, "dddd")... It fired the data validation error saying cannot use formula on date time format... It's really weird. I was trying to get the days like Monday, Tuesday, etc on a separate column.

 

[hollyanneu2]

What exactly to you require?
Possibly something in the following will help.
You can use custom formatting or a formula.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Fri 19-Feb-21 15:23	19	Fri	Friday
3
5c

Cell Formulas
Range	Formula
B2	B2	=DAY(A2)
C2	C2	=TEXT(A2,"ddd")
D2	D2	=TEXT(A2,"dddd")

What exactly to you require?
Possibly something in the following will help.
You can use custom formatting or a formula.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Fri 19-Feb-21 15:23	19	Fri	Friday
3
5c

Cell Formulas
Range	Formula
B2	B2	=DAY(A2)
C2	C2	=TEXT(A2,"ddd")
D2	D2	=TEXT(A2,"dddd")

I used the =text(cell, "dddd")... It fired the data validation error saying cannot use formula on date time format... It's really weird. I was trying to get the days like Monday, Tuesday, etc on a separate column.

Then just type in dddd in the box where it says "Type" and click OK.

This one did it! Thank you!!!!