Date not formatting correctly

Hello all,

My cells are formatted as dates in the format “March 6, 2016”. When I enter the date in that format it will display that way in the cell, but in the function bar it formats it as “03/06/2017”. For my purpose, I need them to all be in the “March 6, 2017” format in both the cell and the formula bar. I really don’t care if the cells are formatted as a date or text, but if I format the cells as text, many of the dates change to a five digit number that is not a date. I have also tried copying and pasting the data to a new sheet by only pasting the values, but then some values paste as a five digit number that is not a date.

Attached is a copy of the file I am working with. Not all dates do this. Column A and most of column B show the correct format in the cell and formula bar, so look at column C and D to see the issue.

Thanks

Originally Posted by xlnitwit

Hi,

Why does it matter what is in the formula bar? If your cells contain actual dates, rather than text, the date will appear formatted in the formula bar based on your regional settings. If you want the two to be the same, you need to enter the dates as text, which will make them fairly useless for anything else you may wish to do with them in your workbook.

I am connecting this to mapping software. The dates are the date the map was last revised and are listed dynamically on the map at the bottom when printed. The problem is that the dates show up on the map printout as the five digit numbers that display when the formatted is changed to text. I suppose it doesn't have to show in the formula bar as the same format as long as the map software shows the date as the same formatted shown in the cell. My main issue is why it shows some dates as a five digit number when the cells are formatted as text as that is the number that shows in the map software. I would rather just have it formatted as text to prevent any such display issues when the map software connects to it, but again, when I format as text many of the dates show as the five digit number.

Hi there,

The values you have entered in columns A and B are TEXT values, regardless of the fact that they "look like" dates and have been formatted as dates, and Excel is therefore unable to process them as date/numeric values - to see what I mean, add a numeric value (e.g. 7) to any cell in column A, and the result will be a #VALUE! error.

Hope this helps,

Regards,

Greg M

Originally Posted by Greg M

Hi there,

The values you have entered in columns A and B are TEXT values, regardless of the fact that they "look like" dates and have been formatted as dates, and Excel is therefore unable to process them as date/numeric values - to see what I mean, add a numeric value (e.g. 7) to any cell in column A, and the result will be a #VALUE! error.

Hope this helps,

Regards,

Greg M

That makes sense, but is there a way to paste the dates as only text? I am fine with them all being text. As explained above to others, this is linked to map software that displays the contents of the cell, but the software it is linked to displays the dates as the 5 digit number instead of a date. I would think I could copy and paste the date as only values and it would paste the exact values shown in the cell, but it still pastes the 5 digit number for dates. It does not matter how they are formatted, text or date, as long as I can get rid of the underlying 5 digit number as that is the number that ends up printing when linked to my map software.

Originally Posted by MrShorty

You can use the =TEXT() function to convert any number to a text string in a specific number format. So, something like =TEXT(42800,"dd-mmm-yyyy") will return the text string "06-Mar-2017". You could then paste this into another text string, or use CONCATENATE() functions/operations to add the date as text into a larger text string.

TEXT() function help file: https://support.office.com/en-us/art...8-93d29371225c
Number format codes help file: https://support.office.com/en-us/art...2-09fab54be7f4

If I am understanding that correctly, I would have to create that formula for each 5 digit value, correct? So one formula for 42800, a separate formula for 42801, and so on. I may as well use find and replace, but either method is more time consuming that I would like. Thanks for the suggestions though.

I am starting to suspect that this is a glitch in the mapping software (ArcMap).
I've noticed some maps show the 5 digit number, some the format "03/06/2017" and some the "March 6, 2017" format,
and it seems to happen at random. Still formatting all dates as text would be a work around as then ArcMap
wouldn't have to "decide" which format to display (apparently at random), it would all be plain text. I suppose there is
no way to do this though unless I retype them, find and replace or use formula =TEXT(42800, March 6, 2017)
but I can not simply copy that formula down as the dates are not in consecutive order, many dates are skipped.