Date Format issue

[mgstilwell]

A1 is start date
A2 is finish date
A3 is total days between the dates

I am trying to copy and paste a file with hundreds of start/finish dates onto another file which had previously been formulated to calculate the two cells and give me the total days between the two dates. Problem is the dates input within the first sheet look like this Jul-9-20, the sheet I am copying to was originaly setup for this format 7/9/2020. When I try to copy and paste the 3rd cell gives me an error because it does not recognize Jul-9-20 format? Any help is greatly appreciated!

 

[Joe4]

I don't think it is the Cell Formatting causing the issue, but rather how the data was entered.
Excel actually stores dates as numbers, specifically the number of days since 1/0/1900.
So, you can change the cell formatting on any date, and it will not change the underlying date. Excel really sees the underlying value, which you can see by changing any valid date entries to "General" format.

If you are getting errors, it is probably because those values are entered as Text instead of Dates/Numbers.
There are two tell-tale signs of this.
1. If the entry is left-justified. By default, all text entries are left-justifed, and all valid date/numeric entries are right-justified.
2. If you change the format of the cell to "General", and it still shows "Jul-9-20". That is because formatting only affects numeric entries, not text ones.

So you will need to convert those "text" entries to valid dates.
If they ALL are like that, you can select the whole column, and do a "Text to Columns" (found on the Data menu), selecting the Date option in Step 3 with the "MDY" option.
If only some are entered as Text, if you sort the column, all the Text entries should fall to the bottom of that list.
If there are only a few, you can fix manually. If there are a bunch, copy them to a blank column, do the "Text to Columns" trick on them, and then move them back.

 

[mgstilwell]

I will try! Thank You