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

bshaffer

New Member
Joined
Aug 19, 2016
Messages
2
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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:
1604627399711.png


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

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

3. it has an error but still works
1604627238116.png


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.
 
Upvote 0
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.

1604632001786.png
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,730
Members
448,294
Latest member
jmjmjmjmjmjm

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