Forum Discussion
converting text string to date time format
Assuming that all values use abbreviated month names Jan, Feb, etc. (May in your example is ambiguous!), try
=CONCATENATE(MID(F2,5,2),"",MID(F2,1,3),"-",MID(F2,9,4))+TRIM(RIGHT(F2,11))
Format the cell(s) with the formula as date and time.
You can also use Power Query (Data > From Table/Range). It will automatically convert the values to real date/time values.
I'm sorry, I do not understand your question. What exactly is the input and what is the desired output?
My time in the format hh:mm:ss. In this format it will not give u answers for =LEFT(G10,2) or =RIGHT(G10,5).
However, if time in the format hh.mm.ss it does allow those calculations.
I could only change them one by one to that format by editing number in Time. When customised it does not work.
Is there any way of changing more than 500 entries to that format (hh.mm.ss) as Time.
If you have a time such as 01:23:45 in cell G10, you can use =TEXT(G10, "hh") to return "01", and =TEXT(G10, "mm:ss") to return "23:45".
- The whole purpose of this is to convert the time duration of 06:39:27 to 6,65 hours through
06.39.27 (in G10) as time
=LEFT(G10,2) = 06 (in J10)
=RIGHT(G10,5) = 39.27 (K10)
=39.26/60 = 0,65 (in L10)
=J10+L10 = 6,65 hours
