Jul 19 2019 11:34 AM
JI have loaded date from a database and want to convert that data to time but it does not work. The format is now standard en looks like this. 164833. If i convert it to time then it becomes 00:00:00. Does anyone know what to do. In this format i can not use it for proces mining.
Jul 19 2019 12:16 PM
Hi,
If this notation 164833 means hhmmss, then you can use this formula to convert it to proper time format:
=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Hope that helps
Jul 19 2019 12:17 PM
Jul 19 2019 12:22 PM
@Gijsl so if you have a serial date (just a number), then use this formula in a cell (like D8) '=TEXT(C8,"m/d/yyyy")'
does that help?
Jul 19 2019 12:26 PM
Jul 21 2019 10:21 AM
Jul 21 2019 10:30 AM
Jul 21 2019 10:38 AM
It is almost perfect but de conversion is wrong with time before noon. For example 93031 is converted to 21:03:31. But it is in de morning en not in the evening. And 84340 is converted in 12:34:40. Do you know what i am doing wrong.
Jul 21 2019 11:07 AM
Sorry, it seems that the formula has a bug!
I've fixed it as follows:
=IF(LEN(A1)=5,TIME(LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2)),
TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)))
Jul 21 2019 04:04 PM
Treating the original as a number, convert first to correctly formatted text, then back to a time value
= TIMEVALUE( TEXT(number, "00\:00\:00") )
Aug 27 2019 02:06 AM