# Day name with TEXT function not working



## Tristram_ZX81 (Dec 21, 2022)

Hello. I'm using Office 365. I understand the formula in J5 below should be returning the text MONDAY as 1/16/2023 is a Monday. But for some reason it's simply returning a copy of the date. Please can someone advise?


----------



## RoryA (Dec 21, 2022)

It looks like your date is actually text (since it's left aligned in the cell) so, assuming your regional settings are US, try using 
	
	
	
	
	
	



```
=TEXT(D5+0,"DDDD")
```


----------



## Tristram_ZX81 (Dec 21, 2022)

Thanks Rory but that hasn't worked.... Any ideas?


----------



## Alex Blakenburg (Dec 21, 2022)

Try this slight variation on Rory's formula.
DateValue is slightly more forgiving than using + 0


```
=TEXT(DATEVALUE(D5),"DDDD")
```


----------



## Tristram_ZX81 (Dec 21, 2022)

Thanks Alex. Sadly, that one just returns  #VALUE!...


----------



## JvdV (Dec 21, 2022)

That's probably no valid date entry. Depending on your settings this will stay a string since there may not be a 16th month. Can you do the following:

* Select D5
* Data > Text to Columns
* Fixed Width > Next > Next
* Date 'MDY' > Finish

Then your regular 
	
	
	
	
	
	



```
=TEXT(D5,"dddd")
```
 should work again.


----------



## Alex Blakenburg (Dec 21, 2022)

Are you able to share an XL2BB rather than a picture ?
How does D5 get populated ?
Does =ISTEXT(D5) return True or False
If TRUE then if you do =LEN(D5) does it return 9 ?
What is your system date format mm/dd/yyyy or dd/mm/yyyy ?


----------



## Tristram_ZX81 (Dec 21, 2022)

Thanks JvdD, that worked!


----------



## jdellasala (Dec 21, 2022)

No DATE function is needed. Put the formula =D5 into cell J5 and give the cell the custom format dddd. Book1AB112/19/2022MondaySheet1Cell FormulasRangeFormulaB1B1=A1


----------



## Peter_SSs (Dec 21, 2022)

If the date is a text string in US format and your system is non-US date format then a couple of formula choices might be


```
=TEXT(DATEVALUE(TEXTJOIN("/",,CHOOSECOLS(TEXTSPLIT(D5,"/"),3,1,2))),"dddd")
```


```
=TEXT(MID(SUBSTITUTE(D5,"/","/"&LEFT(D5,FIND("/",D5)),2),FIND("/",D5)+1,10)+0,"dddd")
```


----------



## Tristram_ZX81 (Dec 21, 2022)

Hello. I'm using Office 365. I understand the formula in J5 below should be returning the text MONDAY as 1/16/2023 is a Monday. But for some reason it's simply returning a copy of the date. Please can someone advise?


----------



## jdellasala (Dec 21, 2022)

I didn't notice earlier that the date was text instead of a date. The formatting trick will still work, but the DATEVALUE function is needed. Book1AB112/19/2022MondaySheet1Cell FormulasRangeFormulaB1B1=DATEVALUE(A1)Cell B1's format is *dddd*. Cell A1 is forced as text using an apostrophe before the date - '12/19/2022.


----------



## Peter_SSs (Dec 21, 2022)

jdellasala said:


> The formatting trick will still work


Only if the OP's date system is m/d/y format. This is what it does for me with system date format d/m/y
Also refer to posts 4 & 5 where DATEVALUE was already suggested.

22 12 21.xlsmDE51/16/2023#VALUE!DayCell FormulasRangeFormulaE5E5=DATEVALUE(D5)

.. and in any case that would not actually return "Monday" in the cell, it will only *appear *that way due to the cell formatting. The underlying cell value would still be the full date numerical date value of 44942


----------



## Alex Blakenburg (Dec 21, 2022)

Per a previous thread the OPs computer is set to UK format dd/mm/yyyy and it sounds to me that his US date format issue has not been resolved.
I think Tristram needs to follow through on that.
Excel is assuming date based on American format

Typically when there is a mismatch in the days <=12 will be read as a date with the days and months reversed and days > 12 will be treated as text.
This is similar to Peter's but will work when the day <=12 and Excel is treating it as a date.


```
=TEXT(TEXTJOIN("/",,(CHOOSECOLS(TEXTSPLIT(TEXT(D5,"dd/mm/yyyy"),"/"),2,1,3)))+0,"dddd")
```

It would still be much better to address the underlying date formatting issue.


----------

