text function not showing correct day

[jb]

Hi Helpers,

I am entering dates in dd/mm/yyyy format in column B. Format of this column is text. I am using text function to display day in column C.

So, far it was working perfectly. But today, when I entered date 02/06/2024, it has started showing TUE in place of SUN.

I found the reason that it is considering 02 as Month and 06 as Day. If I change it to 06/02/2024 it shows SUN.

It is very weird behaviour. I want to enter date in dd/mm/yyyy format only. I don't know what to do.

Kindly help.

 

[AliGW]

What formula are you using? You've not included it in the sample file. Please check that your locale in Excel is NOT set to US (File > Options > Language - needs to be set as UK). Also check your Windows date/time settings for the same.

My locale is all set to UK and it works fine for me.

=TEXT(B17,"ddd")

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A​	B​	C​	D​	E​
4​	1	12/06/2023	Mon		Mon
5​	2	13/06/2023	Tue		Tue
6​	3	14/06/2023	Wed		Wed
7​	4	15/06/2023	Thu		Thu
8​	5	16/06/2023	Fri		Fri
9​	6	17/06/2023	Sat		Sat
10​	7	18/06/2023	Sun		Sun
11​
12​
13​	160	29/05/2024	Wed		Wed
14​	161	30/05/2024	Thu		Thu
15​	162	31/05/2024	Fri		Fri
16​	163	01/06/2024	Sat		Sat
17​	164	02/06/2024	Tue		Sun

Sheet: Sheet1

 

[jb]

What formula are you using? You've not included it in the sample file. Please check that your locale in Excel is NOT set to US (File > Options > Language - needs to be set as UK). Also check your Windows date/time settings for the same.

My locale is all set to UK and it works fine for me.

=TEXT(B17,"ddd")

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A​	B​	C​	D​	E​
4​	1	12/06/2023	Mon		Mon
5​	2	13/06/2023	Tue		Tue
6​	3	14/06/2023	Wed		Wed
7​	4	15/06/2023	Thu		Thu
8​	5	16/06/2023	Fri		Fri
9​	6	17/06/2023	Sat		Sat
10​	7	18/06/2023	Sun		Sun
11​
12​
13​	160	29/05/2024	Wed		Wed
14​	161	30/05/2024	Thu		Thu
15​	162	31/05/2024	Fri		Fri
16​	163	01/06/2024	Sat		Sat
17​	164	02/06/2024	Tue		Sun

Sheet: Sheet1

Thank you so much. I changed settings and it works fine now. Thanks again.

 

[vletm]

jb​

Your I am entering dates in dd/mm/yyyy format in column B. Format of this column is text.

Is there any reason ... why that B-column is text?

Would it be more normal - that it should be formatted as date before entering dates?