Date formatting

rwilding

Board Regular
Joined
Jul 17, 2003
Messages
103
I am trying to have a user type for example: 1/2/3 meaning 1st Feb 2003, and have Excel automatically convert it to 01/02/2003. However when I select the custom date option: dd/mm/yyyy in the format cells window, in the formula bar is 02/01/2003, the American format.
How can I get both displayed dates to read 01/02/2003 or alternatively 01/02/03.


Thanks,
Richard
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Unfortunately that now displays 02/01/03 in the cell, and 01/02/03 in the formula bar.
I require both of the values to be the same, 01/02/03.
Is there anyway of changing the international date settings, as I suspect that on my PC it is set to US format.


Thanks,
Richard
 
Upvote 0
Windows control panel, regional settings, date. Which will also affect other windows pgms.
 
Upvote 0
Thanks for the help,

Unfortunately even though the settings are all UK, and the date format is set as dd/mm/yyyy, Excel still converts the date into mm/dd/yyyy format.

I am confused. Has anyone got a suggestion for what the problem may be?
It is important that both the display and the formula bar show the date in the correct format, as I am analysing the data and must have UK date format.


Thanks,
Richard
 
Upvote 0
I should say that the date when I enter eg. 01/02/03 trying for 1st Feb 03, Excel understands this as 2nd Jan 03.
Even if I set the format as mm/dd/yy, Excel still has this as 2nd Jan 03. I need it to be actually 1st Feb 03, despite what is displayed.
My regional settings are set to UK settings, ie. should display 1st Feb 03.

Do you know what is wrong?


Thanks,
Richard
 
Upvote 0
I cannot explain that. If I change the "Short Date" format where I describe above, that instantly applies to how the dates are shown in the formula bar.
 
Upvote 0
I have identified my problem. The date format was following the UK settings until I pasted the following code into the workbook, designed to force the contents of the workbook to be capitalised.
Can something be added to force the date format to stay as the UK settings, not switch to US.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As

Excel.Range)
Dim c As Range

Application.EnableEvents = False
For Each c In Target.Cells
c = UCase(c.Text)
Next
Application.EnableEvents = True
End Sub




Thanks,
Richard
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top