Excel date format problem

[nahean]

I am using Microsoft excel 2007 in a windows 10 PC.
I have to use/write excel date in dd/mm/yyyy format. I have already changed the system date and excel cell format into this but still there are some problem,

1. I have to write month number first then the day to display the date in that format e.g. to display 07 February 2023 in this format (07/02/2023) i have to write 02 first and then 07.
2. The formula bar always shows month first.
3. I have to use a VBA to save the file as pdf where the date is taken as the part of pdf file name but now the pdf file is fail to create with the date format.

Note: previously i use the same excel file in a windows 7 PC and everything was fine there. After i switch to windows 10 the problem arises.

Can anyone help me to find a good solution?

 

[James006]

Hi,

You should take a look at your windows Regional Settings ... (Start > Control Panel )

 

[awoohaw]

I don't understand what you are saying is wrong: inputting both (in US based settings) "02/07/2023" and "07 February 2023" give a date value of 44964. What values are you getting in your worksheet?

WorkBook1.xlsx
	B	C
8	44964	=DATEVALUE("02/07/2023")
9	44964	=DATEVALUE("07 February 2023")
Sheet5

Cell Formulas
Range	Formula
B8	B8	=DATEVALUE("02/07/2023")
C8:C9	C8	=FORMULATEXT((B8))
B9	B9	=DATEVALUE("07 February 2023")

 

[shinigamilight]

Your system language must be US English or your system date must be m/d/y. Excel works on ur regional settings.

 

[nahean]

I don't understand what you are saying is wrong: inputting both (in US based settings) "02/07/2023" and "07 February 2023" give a date value of 44964. What values are you getting in your worksheet?

WorkBook1.xlsx
	B	C
8	44964	=DATEVALUE("02/07/2023")
9	44964	=DATEVALUE("07 February 2023")
Sheet5

Cell Formulas
Range	Formula
B8	B8	=DATEVALUE("02/07/2023")
C8:C9	C8	=FORMULATEXT((B8))
B9	B9	=DATEVALUE("07 February 2023")

Yes, got your point and u r right.

My point is I have to write month number first and in formula bar month number shows first. After finishing it shows days first. Though my main purpose is served but it looks weird when i have to edit any date.
Btw in windows 7 this didn't happen.

Another thing about VBA of saving file in pdf i cant use any date cell as pdf title.

(Note: I am using excel 2007 in both windows 7 and 10 and the same excel file is used in both case).

 

[awoohaw]

Yes, got your point and u r right.

My point is I have to write month number first and in formula bar month number shows first. After finishing it shows days first. Though my main purpose is served but it looks weird when i have to edit any date.
Btw in windows 7 this didn't happen.

Another thing about VBA of saving file in pdf i cant use any date cell as pdf title.

(Note: I am using excel 2007 in both windows 7 and 10 and the same excel file is used in both case).

Okay, I see what you mean. And especially when your cells are in an unfamiliar format but you see your most familiar format up top. It can be confusing when clicking in the formula bar to edit. Everybody should just do dates one way! I prefer yearmonthday, with spacing zeros. That makes sorting easier if for some reason that gets converted into a text string.

And I guess changing your regional setting doesn't make sense as you'll want your local setting on all of your other work.

Best wishes!

 

[nahean]

Okay, I see what you mean. And especially when your cells are in an unfamiliar format but you see your most familiar format up top. It can be confusing when clicking in the formula bar to edit. Everybody should just do dates one way! I prefer yearmonthday, with spacing zeros. That makes sorting easier if for some reason that gets converted into a text string.

And I guess changing your regional setting doesn't make sense as you'll want your local setting on all of your other work.

Best wishes!

Yes, got it. Regional setting is the reason behind the problem. After changing it everything works. Thanks again.