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

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]1[/td][td=bgcolor:#FFFFFF]12/06/2023[/td][td=bgcolor:#FFFFFF]Mon[/td][td][/td][td]Mon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]2[/td][td=bgcolor:#FFFFFF]13/06/2023[/td][td=bgcolor:#FFFFFF]Tue[/td][td][/td][td]Tue[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]3[/td][td=bgcolor:#FFFFFF]14/06/2023[/td][td=bgcolor:#FFFFFF]Wed[/td][td][/td][td]Wed[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]4[/td][td=bgcolor:#FFFFFF]15/06/2023[/td][td=bgcolor:#FFFFFF]Thu[/td][td][/td][td]Thu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]5[/td][td=bgcolor:#FFFFFF]16/06/2023[/td][td=bgcolor:#FFFFFF]Fri[/td][td][/td][td]Fri[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]6[/td][td=bgcolor:#FFFFFF]17/06/2023[/td][td=bgcolor:#FFFFFF]Sat[/td][td][/td][td]Sat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]7[/td][td=bgcolor:#FFFFFF]18/06/2023[/td][td=bgcolor:#FFFFFF]Sun[/td][td][/td][td]Sun[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]160[/td][td=bgcolor:#FFFFFF]29/05/2024[/td][td=bgcolor:#FFFFFF]Wed[/td][td][/td][td]Wed[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]161[/td][td=bgcolor:#FFFFFF]30/05/2024[/td][td=bgcolor:#FFFFFF]Thu[/td][td][/td][td]Thu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]162[/td][td=bgcolor:#FFFFFF]31/05/2024[/td][td=bgcolor:#FFFFFF]Fri[/td][td][/td][td]Fri[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]163[/td][td=bgcolor:#FFFFFF]01/06/2024[/td][td=bgcolor:#FFFFFF]Sat[/td][td][/td][td]Sat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]164[/td][td=bgcolor:#FFFFFF]02/06/2024[/td][td=bgcolor:#FFFFFF]Tue[/td][td][/td][td]Sun[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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?