Parsing text to Date & Time doesn't work

[intuitive-reasoning]

I need to parse text date and time values into real date and time

This is what I'm doing:

21.06.2021 20:25:43 =DATEVALUE(TEXT(A1,"DD.MM.YYYY")) + TIMEVALUE(TEXT(A1,"HH:MM"))

I'm getting #VALUE! error

Thakns.

[AliGW]

Maybe:

=DATEVALUE(TEXT(LEFT(A1,10),"DD.MM.YYYY")) + TIMEVALUE(TEXT(RIGHT(A1,7),"HH:MM"))

[JohnTopley]

try

date

=DATEVALUE(SUBSTITUTE(MID($A$1,1,8),".","/"))

format cell as required

time

=TIMEVALUE(MID($A$1,12,8)) or =TIMEVALUE(RIGHT($A$1,8))

format cell as required


=DATEVALUE(SUBSTITUTE(MID($A$1,1,8),".","/")) & =TIMEVALUE(MID($A$1,12,8))

[MrShorty]

Is your operating system set up to sue "." as the delimiter in short date format? Is it also setup to recognize DMY dates? Your profile says that you are in the US. I'm pretty sure the default US regional setting in windows is to use "/" and MDY for date formatting (mm/dd/yyyy maybe??). The DATEVALUE() function uses your system default when attempting to recognize text as date, so, if your operating system is set to use something else, Excel's DATEVALUE() function will not be able to recognize the date.

The way I might approach this is to use Text to columns to split the text into four fields using "." as one delimiter and space as a second delimiter. Then recombine the numbers into the desired date/time serial number. With date, month, year separated into numbers and time stored as a number (my Text to columns automatically converted all field into numbers), you can recombine into a date/time serial number using DATE() and TIMEVALUE(). Assuming you parsed the text into A1:D1, then =DATE(C1,B1,A1)+D1 would work.

[intuitive-reasoning]

Thanks, this worked.

[FlameRetired]

intuitive-reasoning, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.