CODEWITHSUNDEEP
excel
WJA
WJA

Reputation: 7004

How to format different date cells into the same format?

I have a column in which there is not a consistent formatting of the date cells.

Specifically, I have so far observed two cases:

Case A: 1/30/14 20:00

Case B: 1-8-2015 9:00

Case B seems not to react to being transformed to a date format (i.e., it is most likely a string). Case A is in a proper date format which I can modify.

My idea was to simply do a ctrl+f and replace "/" with "-" and then copy paste it into a new worksheet as unicoded text (this didnt work because of the year difference).

I am also unsure whether this would be a consistent way of dealing with it. Are there any other known methods that can provide a more robust and consistent way of handling this type of formatting?

Upvotes: 0

Views: 43

Answers (1)

gtwebb
gtwebb

Reputation: 3011

I would try using the datevalue function which tries to convert a string into a proper date. It errors if it is given a number (an actual date) so I would use the following formula. If it sees a number instead of a string it just returns the cell value.

=IFERROR(DATEVALUE(A1),A1)

Upvotes: 1

Related Questions