Forum Discussion
Solved
I'm unable to subtract these two properly formatted dates
I have two date columns, but I'm unable to do a simple subtraction. I get the #VALID error bar as an output. I've tried highlighting the two columns of numbers that have dates and converting them to ...
The values look like dates, but they are actually text values. A tell-tale sign is that they are left-aligned.
Select a column with text dates, then click Text to Columns on the Data tab of the ribbon.
Click Next>> twice.
In step 3, select date and make sure that MDY is selected. Then click Finish.
Repeat for each column with text dates.
Out of curiosity, why didn't my solution of converting the dates by highlighting the column and using Format Cells --> Date work?
I thought that by changing the format of the data, that should work, but it didn't in this case. Your solution worked perfectly.
If Excel sees a value as text, the number formatting will be ignored.
Text to Columns converts the values to 'real' dates.
- Thank you!