Forum Discussion
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 dates.
Here's an image of what the data looks like: https://imgur.com/a/DjY7sMx As you can see in the formula bar, the numbers show up exactly like mm/dd/yyyy .
I also found out that the date columns don't sort from A-->Z or Z-->A properly. Here's an image of what happens when I try: https://imgur.com/RKeRwXK I noticed that it's sorting by only the months, and it ignores the dd and the yyyy.
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.
5 Replies
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.
HansVogelaar
Thanks! That worked perfectly.
