Share via

Convert Text to Date

Anonymous
2023-10-06T15:14:32+00:00

Hi,

A report I have downloaded has a date of 03.04.2023 in which I assume is text - if enter =datevalue() on the cell it produces a VALUE! error.

How do I convert to a normal date format? I have already tried formatting?

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-10-06T15:23:16+00:00

You may try text to column feature.

Text to column>Next >Next>Choose the format of your date (DMY or MDY) > Finish.

It will convert to date value.

Result:

Image

Image

.

Or formula

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-07T00:52:24+00:00

    =--WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~(\d{2}).(\d{2}).(\d{4})~$3-$1-$2~" & A2)

    or

    =TEXT(--WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~(\d{2}).(\d{2}).(\d{4})~$3-$1-$2~" & A2),"mm/dd/yyyy")

    Was this answer helpful?

    0 comments