Date format issue - dd/mm/yyyy

[princessdy]

Hi! I am desperate. I purposely registered just to ask this question. Hope you can help.

I have a shared file in server. I change all date values to dd/mm/yyyy format. But every time another user opens it, the format becomes d/m/yyyy. I prefer dd/mm/yyyy so all numbers are aligned.

How do I solve this issue? I cannot possibly ask all users to change their PC settings or whatnot.

 

[Anthony47]

Do you mean that you selected the columns that contain dates and formatted the cells for date format "dd/mm/yyyy"?

 

[princessdy]

Do you mean that you selected the columns that contain dates and formatted the cells for date format "dd/mm/yyyy"?

Yes. The file contains dates, numbers and client info. I selected the columns showing dates and formatted them to dd/mm/yyyy. But it keeps reverting to d/m/yyyy eg 3/1/2020 instead of 03/01/2020. I want uniform 2digits/2digits/4digits format. The file is shared with other users. Is there anyway I can override the format to always show dd/mm/yyyy regardless of who uses the file?

 

[Anthony47]

That's strange... Are all the users using the same Office version and the same regional settings?
Is this a .xlsx or .xlsm? Are you allowed to add a macro, that would reformat the dates at file open? Anyway I would suggest formatting as dd-mmm-yyyy

 

[princessdy]

Yes we are using the same Office 365 version. Right now the file is .xlsx. I can’t be sure of the regional settings but in my mind regional settings only affect dd/mm vs mm/dd. And we are all using day-month date format. But I can be wrong. We are allowed to use macro. But I am not a coder.

I will try dd-mmm-yyyy format. I am unaware it is different from dd/mm/yyyy format

 

[SpillerBD]

"Date formats that begin with an asterisk(*) respond to changes in the regional date and time settings that are specified for the operating system."
Short Date appears to have a default of "m/d/yyyy" for Country/Region (English (United States)).
I am confused as well.
Are you assigning your desired format by typing it in using the Custom Format option?
Or is the format becoming something like "[$-en-US] dd/mm/yyyy" ?

(Not applicable for the OP, however, when working with the possible international confusion, I use a format that does short text on the month, like "DD-MMM-YYYY" or "MMM DD, YYYY" yielding, "29-JUL-2022" )

 

[Anthony47]

Don't miss the message from SpillerBD, above...

dd/mm/yyyy will display 29/07/2022, whereas dd/mmm/yyyy will display 29/jul/2022 or dd-mmm-2022 will display 29-jul-2022

If you may convert the file to xlsm (macro enabled) then do the following:
-open the file and select a worksheet where you don't need to format cells
-start recording a macro; see Automate tasks with the Macro Recorder for the basic information. Chose to record the macro within "this workbook" and assign it the name Dateformat

Then:
-select the first sheet to format
-select the first column (or range) to format
-apply the desired format
-repeat with other columns or ranges in the sheet
-select one by one the other sheets to format, and apply the format one after the other
-when you are done, select the first sheet and press the Esc key
-stop the recording
Now your "Sub Dateformat" is ready

But we want to execute it whenever the workbook be open:
-see Automatically run a macro when opening a workbook for information
-open the Visual Basic environment and locate the module ThisWorkbook
-doubleclick on the module ThisWorkbook
-insert the following code into the empty code area:

Private Sub Workbook_Open()
    Call Dateformat
End Sub

Return to excel and save the file as .xlsm

Close and reopen the file; you will probably see the sheets and the areas that get selected and formatted in sequence. Il this "flickering" disturbs then let's modify Sub Dateformat code. From excel:
-type Alt-F8, select Dateformat from the list of the available macro, press Edit. This will open the Visual basic interface and will show the code of the recorded macro.
-add these two instruction at the beginning and just befor the end:

Sub Dateformat()
'
Application.ScreenUpdating = False          '<<< ADD
'
'here the recorded code
'
Application.ScreenUpdating = True           '<<< ADD
End Sub

If you save, close and reopen the file now the process of formatting should be not visible

Try...

 

[princessdy]

Wow! Thanks so much for this! I will try to follow closely since I’m not very adept at macro.

 

[princessdy]

"Date formats that begin with an asterisk(*) respond to changes in the regional date and time settings that are specified for the operating system."
Short Date appears to have a default of "m/d/yyyy" for Country/Region (English (United States)).
I am confused as well.
Are you assigning your desired format by typing it in using the Custom Format option?
Or is the format becoming something like "[$-en-US] dd/mm/yyyy" ?

(Not applicable for the OP, however, when working with the possible international confusion, I use a format that does short text on the month, like "DD-MMM-YYYY" or "MMM DD, YYYY" yielding, "29-JUL-2022" )

Regarding your first question, I am typing it using the Custom format option. I don’t think I ever chose [$-en-US] dd/mm/yyyy if ever there was an option like that.

Does it help that I am from Singapore? And our office PC all have Singapore date settings.

 

[princessdy]

Wow! Thanks so much for this! I will try to follow closely since I’m not very adept at macro.

Don't miss the message from SpillerBD, above...

dd/mm/yyyy will display 29/07/2022, whereas dd/mmm/yyyy will display 29/jul/2022 or dd-mmm-2022 will display 29-jul-2022

If you may convert the file to xlsm (macro enabled) then do the following:
-open the file and select a worksheet where you don't need to format cells
-start recording a macro; see Automate tasks with the Macro Recorder for the basic information. Chose to record the macro within "this workbook" and assign it the name Dateformat

Then:
-select the first sheet to format
-select the first column (or range) to format
-apply the desired format
-repeat with other columns or ranges in the sheet
-select one by one the other sheets to format, and apply the format one after the other
-when you are done, select the first sheet and press the Esc key
-stop the recording
Now your "Sub Dateformat" is ready

But we want to execute it whenever the workbook be open:
-see Automatically run a macro when opening a workbook for information
-open the Visual Basic environment and locate the module ThisWorkbook
-doubleclick on the module ThisWorkbook
-insert the following code into the empty code area:

Private Sub Workbook_Open()
    Call Dateformat
End Sub

Return to excel and save the file as .xlsm

Close and reopen the file; you will probably see the sheets and the areas that get selected and formatted in sequence. Il this "flickering" disturbs then let's modify Sub Dateformat code. From excel:
-type Alt-F8, select Dateformat from the list of the available macro, press Edit. This will open the Visual basic interface and will show the code of the recorded macro.
-add these two instruction at the beginning and just befor the end:

Sub Dateformat()
'
Application.ScreenUpdating = False          '<<< ADD
'
'here the recorded code
'
Application.ScreenUpdating = True           '<<< ADD
End Sub

If you save, close and reopen the file now the process of formatting should be not visible

Try...

I tried it. I opened the new .xlsm file and there was no flickering so I stopped there.

Should there be any changes to this ie when the other users experience the flickering or what not, we will try to follow the next steps above.

Thanks a lot!! My above issue has been a puzzle to me for years. I initially thought the other users are reverting back the format to d/m/yyyy but there are not doing any formatting to the file at all.

When I change the format every time to my desired format dd/mm/yyyy and close and open the file again just to check if the format changed - it doesn't. So I came to the conclusion that the other users' PC must be automatically reverting the date format to d/m/yyyy.

You are a life-saver!