Date format in cell does not match date format in formula bar

[bshaffer]

Please help. I'm having trouble getting the date format in the formula bar to match the date format in the cells of my worksheet.

I have my dates formatted in my cells as m/dd/yy however the formula bar shows m/dd/yyyy. I'm trying to get the formula bar to return only the 2 digit year format I have in my cells.

So far I've tried the following with no luck.

1. Set Region on Control Panel
2. Disable Macros
3. Uncheck 1904 date system
4. Cut column over to Notepad and paste back in Excel
5. Change to Text format
6. Change to Custom format

Here's my worksheet.

https://www.dropbox.com/s/tqvcbyw8dmsjm1q/data formatting isssuing.xlsx?dl=0


I've spent a lot of time trouble-shooting this and I'm getting really frustrated. Please help. Thank you!

 

[sheetspread]

Look here:

How to change date format from dd-mm-yyyy to dd-mm-yy in formula bar of MS-EXCEL? - Super User

 

[bshaffer]

Thanks for the link and taking the time to respond sheetspread! I now know it can't be done and can communicate that to my stakeholders. Much appreciated.

 

[hijinxkid]

Please help. I'm having trouble getting the date format in the formula bar to match the date format in the cells of my worksheet.

I have my dates formatted in my cells as m/dd/yy however the formula bar shows m/dd/yyyy. I'm trying to get the formula bar to return only the 2 digit year format I have in my cells.

So far I've tried the following with no luck.

1. Set Region on Control Panel
2. Disable Macros
3. Uncheck 1904 date system
4. Cut column over to Notepad and paste back in Excel
5. Change to Text format
6. Change to Custom format

Here's my worksheet.

Dropbox - Error formatting isssuing.xlsx?dl=0


I've spent a lot of time trouble-shooting this and I'm getting really frustrated. Please help. Thank you!

Hi,
I was having the same issue and found a way for it to work for me. It gives me a format error but I am still able to use it for my purposes. I have included a screenshot. In short:
Problem:

1. I created a new column next to the date and entered this formula: =text(a2,"m/d/yy/")

2. select your newly created column, copy, then paste values

3. it has an error but still works

Took me a bit to figure it out but hope it helps someone. I needed to convert this date format for a graph I was creating and needed it to fit a certain way LOL.

 

[MARK858]

If you don't like seeing it show as an error you can turn it off by unchecking it in File-Options-Formulas-Error checking rules.

 

[pedro-egoli]

I got this to work from Mr google
Follow these steps:

1. Select the cells you want to format.
2. Press CTRL+1.
3. In the Format Cells box, click the Number tab.
4. In the Category list, click Date.
5. Under Type, pick a date format. ...
6. If you want to use a date format according to how another language displays dates, choose the language in Locale (location)

I initially input 10/1/2020 and by following above arrived at

1/10/20

 

[MARK858]

I got this to work from Mr google
Follow these steps:

1. Select the cells you want to format.
2. Press CTRL+1.
3. In the Format Cells box, click the Number tab.
4. In the Category list, click Date.
5. Under Type, pick a date format. ...
6. If you want to use a date format according to how another language displays dates, choose the language in Locale (location)

I initially input 10/1/2020 and by following above arrived at

Does that change how it displays in the formula bar (as per the request) for you?

 

[pedro-egoli]

OK this is what I did.
In cell typed 10/1/2020
1. clicked Ctrl+1
2. Selected English(United States) under location
and also Type 3/14/12
3 clicked OK
Formula bar still showed 10/1/2020 howeve, selected cell showed 1/10/20

 

[MARK858]

Formula bar still showed 10/1/2020 howeve, selected cell showed 1/10/20

That is the issue, the original poster and hijinxkid want the cell and the formula bar to match.

 

[pedro-egoli]

That is the issue, the original poster and hijinxkid want the cell and the formula bar to match.

Sorry read it the wrong way.
Good to see there was a workaround