CODEWITHSUNDEEP
excelexcel-formula
Reddy
Reddy

Reputation: 11

How do I convert date-time to date in Excel?

20170302092100 should be 2017-03-02.

Have used this formula:

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

but it is getting the result as 2/28/2017.
YYYY and MM is correct, not sure why date is incorrect and where is 28 coming from.

Upvotes: 0

Views: 141

Answers (2)

Fernando
Fernando

Reputation: 151

The correct formula is:

=DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))

Upvotes: 0

girlvsdata
girlvsdata

Reputation: 1644

Your "RIGHT(B2,2)" formula is getting the last 2 digits of your DateTime, so:

20170302092100

Running your "LEFT()", "RIGHT()" AND "MID()", you get: 2017 3 0

And when you put that into a formula =Date(2017,3,0) you'll get 2/28/2017

The correct formula would be: =DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))

Thanks!

Upvotes: 3

Related Questions