I have just bought MS Office H&S 2019. I have been using excel for years, but have just encountered a strange problem. I am trying to copy a printed spreadsheet on to my computer so that I can answer questions by using excel. I have typed all the dates in that are spread over a 12 month period, I then highlighted them all to change the format, for example, from 01.01 to 01.Jan, or Feb, Mar etc etc. The first thing I noticed, is that excel 2019 will not let me type 01.01, but it gives me 1.01. OK fare enough, but when I highlight all the dates and custom format to read as dd.mmm, it changes all the months to Jan? I have never seen this before and whenever I have used excel, changing/formatting the date has never been an issue. Could someone please advise and point me in the right direction?

Pauly_B Hello, assuming you want dd/mm/yyyy date formatting thing, kindly follow the steps 1. Type in Control panel 2. Select Change date, type or number formats 3. Click on Additional Settings 4. Click on Date tab 5. Type in dd/mm/yyyy inside the Short Date box as seen in the caption below 6. Click OK. This would permanently display dates on your system including in Excel files You can have dd.mm.yyyy or mm.dd.yyyy or any formats that suits you

Pauly_B In addition, you can use Custom Format 1. Select the Dates i.e 21/02/2020 "dd/mm/yyyy" format 2. Execute CTRL + 1 3. Click on Custom 4. Type in dd.mm.yyyy Click OK

Thank you Abiola1 That is what I tried to do, like I have done many times before, but instead of changing the date from, for example 02 (for Feb) it left the day the same as was, but changed all the different months to Jan

Pauly_B Hello, kindly take a screenshot of the date you have so that I can better understand the point

Pauly_B To be specific, which exact date format you want? Is it mm/dd/yyyy, dd/mm/yyyy, mm/yyyy, mm.dd.yyyy or whatever. Let me know the exact one you desire

Excel Date formatting | Microsoft Community Hub

14 Replies

Abiola1
MVP
Feb 21, 2020
Pauly_B

As posited in the first reply, you can have a permanent change to the date format by following the steps highlighted

You can have whatever format you desire dd-mmm, mm-dd-yyyy, dd-mm-yyyy, dd/mm/yyyy etc.
- Pauly_B
  Copper Contributor
  Feb 21, 2020
  Abiola1
  Yeah thank you Abiola, I got that bit and have always understood the how, thank you. My issue was using 2019, why all of a sudden the straight forward formatting that I have always used before, suddenly wasn't doing what it always used to on the countless Excel spreadsheets I have created previously.
  
  Thank you for trying to help anyway
  - Abiola1
    MVP
    Feb 21, 2020
    Custom forgetting works the same across versions.
    
    You're welcome
Abiola1
MVP
Feb 21, 2020
Pauly_B

To be specific, which exact date format you want? Is it mm/dd/yyyy, dd/mm/yyyy, mm/yyyy, mm.dd.yyyy or whatever. Let me know the exact one you desire
- Pauly_B
  Copper Contributor
  Feb 21, 2020
  Abiola1
  - Abiola1
    MVP
    Feb 21, 2020
    Pauly_B
    
    As seen in your reply, you can use the dd-mmmm which will show i.e 22-January. dd-mmm shows i.e 22-Jan
Abiola1
MVP
Feb 21, 2020
Pauly_B

In addition, you can use Custom Format

1. Select the Dates i.e 21/02/2020 "dd/mm/yyyy" format

2. Execute CTRL + 1

3. Click on Custom

4. Type in dd.mm.yyyy

Click OK
- Pauly_B
  Copper Contributor
  Feb 21, 2020
  Thank you Abiola1
  
  That is what I tried to do, like I have done many times before, but instead of changing the date from, for example 02 (for Feb) it left the day the same as was, but changed all the different months to Jan
  - SergeiBaklan
    Diamond Contributor
    Feb 21, 2020
    Pauly_B
    
    If 01.03 is converted to 1.03 that means that dot is not your default separator in date format. Applying dd.mmm format to number 1.03 Excel considers the number 1.03 as January 01, 1900 plus 03/24 hours, more exactly as 1900-01-01 00:43:12. Thus it will be formatted as 01.Jan.
    
    Use your default separator (e.g. 01/03) or change it on dot in Win+R, control international, Advanced.
Abiola1
MVP
Feb 21, 2020
Pauly_B

Hello, assuming you want dd/mm/yyyy date formatting thing, kindly follow the steps

1. Type in Control panel

2. Select Change date, type or number formats

3. Click on Additional Settings

4. Click on Date tab

5. Type in dd/mm/yyyy inside the Short Date box as seen in the caption below

6. Click OK.

This would permanently display dates on your system including in Excel files

You can have dd.mm.yyyy or mm.dd.yyyy or any formats that suits you

Forum Discussion

Excel Date formatting

14 Replies

Resources