Excel doesn't recognize dates

[sam9989]

I'm trying to work with a sheet which has a column of date which has this format "Mar-30 2018 20:44". I tried using aging formula "=TODAY()-DATECELL" but it giving value error.
Looks like column data is not recognized as date type. Please help.

[AliGW]

DATECELL is not a valid function. What is your formula? Do you have a UDF in your workbook? Are you sure your dates really are dates or just text?

[sam9989]

Thanks for the response. "=TODAY()-DATECELL" is the syntax i follow. By DATACELL i mean the cell address of the DATE(Actual formula usage is =TODAY()-D2)

[PaulM100]

Select the entire column, go to Data - Text to Column - Next - Next - Select Date format and Destination as the first cell in the same column - Finish.

[sam9989]

Select the entire column, go to Data - Text to Column - Next - Next - Select Date format and Destination as the first cell in the same column - Finish.

Thanks.Tried this but not working.Date type is not identified even after this.

[AlKey]

It seems to me that this "Mar-30 2018 20:44" is a text string. So, it needs to be converted to a real date.
Try this:
Assuming "Mar-30 2018 20:44" is in cell A1
Enter formula in B1

=INT(TODAY()-SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",", ",2))

v	A	B
1	Mar-30 2018 20:44	107

Or this shorter version

=TODAY()-SUBSTITUTE(A1," ",", ",1)

[sam9989]

Thank you..The data type shows general and i tried changing it to use the formula.Its giving value error after i use the formula.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.