4-digit year date format in Excel 2015

[rucana]

Can anyone help me find a way to format dates w 4 digits (dd/mm/yyyy) instead of 2 (dd/mm/yy) in Excel? It does not appear in the options for Date format and I have already changed the format of dates in the System Preferences and I didn't succeed. Thx!

 

[pigoo3]

I have that option available in my version of Office 2011 (Excel). If I want October 15, 2016...I can format a cell to display 10/15/2016. No problamo.

Nevermind. I see you want day/month/year. I was talking month/day/year.

- Nick

 

[McBie]

In Excel, select the cell, right click, select format cells, select custom, select ( or type ) dd/mm/yyyy ....

That should do the trick.

Cheers ... McBie

 

[MikeinGrange]

In the Format window instead of selecting Date select Custom.
Then in the Type field enter dd/mm/yyyy.

 

[MikeinGrange]

... answered whilst I was typing. Same answer though.

 

[Slydude]

@OP If you routinely use this document as the template for future documents you may find that occasionally your formatting options will spontaneously change to something else. It seems to happen when I save things as a document rather than a template.

I've had this happen when I format a column and try to apply the type of date formatting you're looking for. It's not an issue that the date doesn't like that formatting. Something else is going on. I've seen some suggestions that it's a bug in Excel and other suggestions that it's a memory issue of some sort.

For me, it's not a major annoyance but it is something to be aware of.

 

[rucana]

Dear fellows, thx for all the replies! I appreciate your quickness! The thing is that I don't want "just" to have a 4-digit year format. I know I can "cheat" Excel with Custom or Text formats. I wanted to have the option of a 4-digit year date ready to go on the Date format. I wonder how can I not have it if I have already changed the date format on the Language/Date preferences! It might demand a more advanced solution on my current system. Sorry for being so thorough and precise...

 

[McBie]

With all due respect, I don't understand your question. ( no worries, that's just me .... )
What is it exactly that you want to do ?
You have all the options you need at your fingertips.
But if you format a cell, than it is no longer an option. It is a choice. It is a binary thing.

Cheers ... McBie

 

[Slydude]

@rucana Are you saying that you want the cells already formatted so that when you enter something in the blank cell it appears as a date with a 4 digit year? I'm a bit confused as well.

 

[toMACsh]

Dear fellows, thx for all the replies! I appreciate your quickness! The thing is that I don't want "just" to have a 4-digit year format. I know I can "cheat" Excel with Custom or Text formats. I wanted to have the option of a 4-digit year date ready to go on the Date format. I wonder how can I not have it if I have already changed the date format on the Language/Date preferences! It might demand a more advanced solution on my current system. Sorry for being so thorough and precise...

You want Excel to offer this option, and not have to use Custom, right?

 

[ferrarr]

Is Excel 2015 for Mac?

 

[rucana]

Dear fellows, I will answer to all as it seems you are not understanding what I want. I fact I think you are believing what is happening! Sorry myself. In windows I know the dd/mm/yyyy date format is already there. Maybe In some Macs too but NOT in mine. The project I am working I need to enter the date like this and not as insistently presented (dd/mm/yy). I know I can have a way out by using Text or Custom formats but I want to have the dd/mm/yyyy in the date format, if possible.

 

[rucana]

you understood it correctly @slydude.

 

[rucana]

Yes @ferrarr. 2015 Excel.

 

[rucana]

I have Custom toMACsh, but I want a way to have dd/mm/yyyy on date format, just like the rest of the world! ;-)

 

[rucana]

@slydude, what bugs me is that a collegue of the same project has a Macbook and has no issues with the four-year- digit date...

 

[rucana]

it does not matter the inversion of the day x month, @pigoo3. The problem relates to the year format. I can only enter mm/dd/yy instead of mm/dd/yyyy...
Every time I enter a date it becomes immediately a 2-digit year date. It sucks! As I said both the short and the middle dates formats on my system preferences are set to yyyy! I just don't what to do other than use Custom or Text...

 

[Slydude]

I think I understand why you are seeing two digit years rather than four digit years. Do the following and see if you then have the option for a four digit year setting.

1. Open System Preferences and go to the Date & Time preference pane.
2. Make sure to click the Date & Time tab.
3. Click where it says Open Language and Region then select Advanced. . .
4. In the next window/sheet that appears click the Date tab.
5. In the lune that says "Short" there is a date filled in. The year is probably in two digit format. You should be able to change it to a four digit year.

If I understand correctly you probably only need to do these steps once. I'm not sure if you will have to unlock the padlock on the preference pane.

Now open Excel, right click a cell and choose Format. Now when you go to the Numbers tab and Date formatting the four digit year should be in the drop down list. If you select multiple cells before applying the formatting the change will be applied to all selected cells.

 

[rucana]

Thx @slydude, but I have done those steps and then I "threw the towel" and addressed to you guys here, knowing, almost for sure, that it would be the only intermediate (more advanced moves I wouldn´t know...) way to fix this. Doesn't matter more. I have to move ahead and I will go through Format/Custom. Thx again to all of you!