Home

Matching date input to the formatted date display

%3CLINGO-SUB%20id%3D%22lingo-sub-1179743%22%20slang%3D%22en-US%22%3EMatching%20date%20input%20to%20the%20formatted%20date%20display%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179743%22%20slang%3D%22en-US%22%3EHey%20there%2C%20I%E2%80%99m%20having%20an%20issue%20with%20date%20formatting.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20my%20workplace%2C%20date%20entry%20is%20always%20done%20as%20dd%2Fmm%2Fyy.%20Therefore%2C%20I%20want%20excel%20to%20show%20the%20same%20thing.%20I%20formatted%20the%20cells%20to%20display%20%E2%80%9Cdd-mm-yy.%E2%80%9D%20The%20problem%20is%2C%20it%20seems%20to%20interpret%20the%20input%20as%20yy%2Fmm%2Fdd%20regardless%20of%20the%20format%20I%20use.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20input%20must%20be%20dd%2Fmm%2Fyy%2C%20and%20the%20output%20must%20be%20the%20same.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1179743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1179758%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20date%20input%20to%20the%20formatted%20date%20display%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1179758%22%20slang%3D%22en-US%22%3EThis%20is%20based%20on%20your%20computer's%20regional%20language%20settings.%20That's%20what%20Excel%20defaults%20to%20when%20interpreting%20dates.%20That's%20where%20you%20need%20to%20change%20things.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1180392%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20date%20input%20to%20the%20formatted%20date%20display%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1180392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562076%22%20target%3D%22_blank%22%3E%40Erikstg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20permanently%20change%20the%20Date%20Format%20to%20mm%2Fdd%2Fyyyy%3A%3C%2FP%3E%0A%3CP%3E1.%20Type%20in%20Control%20Panel%20on%20your%20system%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1582054378216.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171845i0F8CABA2F7646FE0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1582054378216.png%22%20alt%3D%22Abiola1_0-1582054378216.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E2.%20Click%20on%26nbsp%3B%3CSTRONG%3EChange%20date%2C%20time%2C%20or%20number%20formats%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E3.%20Click%20on%20Additional%20Settings%3C%2FP%3E%0A%3CP%3E4.%20Select%26nbsp%3B%3CSTRONG%3EDate%26nbsp%3B%3C%2FSTRONG%3Etab%3C%2FP%3E%0A%3CP%3E5.%20In%20the%20Short%20Date%20box%2C%20type%20in%20dd%2Fmm%2Fyyyy%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_1-1582054518148.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171846i7F2856ECBA168701%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_1-1582054518148.png%22%20alt%3D%22Abiola1_1-1582054518148.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E6.%20Click%20OK%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20doing%20this%2C%20all%20the%20dates%20including%20in%20Excel%20spreadsheet%20will%20be%20displayed%20in%20mm%2Fdd%2Fyyyy%20format%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor
Hey there, I’m having an issue with date formatting.

In my workplace, date entry is always done as dd/mm/yy. Therefore, I want excel to show the same thing. I formatted the cells to display “dd-mm-yy.” The problem is, it seems to interpret the input as yy/mm/dd regardless of the format I use.

The input must be dd/mm/yy, and the output must be the same.

Thanks for the help!
2 Replies
Highlighted
This is based on your computer's regional language settings. That's what Excel defaults to when interpreting dates. That's where you need to change things.
Highlighted

@Erikstg 

To permanently change the Date Format to mm/dd/yyyy:

1. Type in Control Panel on your system

Abiola1_0-1582054378216.png

2. Click on Change date, time, or number formats

3. Click on Additional Settings

4. Select Date tab

5. In the Short Date box, type in dd/mm/yyyy

Abiola1_1-1582054518148.png

6. Click OK

 

After doing this, all the dates including in Excel spreadsheet will be displayed in mm/dd/yyyy format

 

Related Conversations