Forum Discussion
converting text string to date time format
hi
I would like to be able to sort some data I get by date and time, however the date field is expressed like so:
May 15, 2023, 6:34:54 PM
how can i convert this to a date time that excel recognises? I've initially done the following where the above date value is in column F, however if I try and add in the time I just get a 00:00:00
=DATEVALUE(CONCATENATE(MID(F2,5,2),"/",MID(F2,1,3),"/",MID(F2,9,4)))
Is there a way I can get excel to recognise this as a date field without doing the above, btw I have already tried a custom format and that does not work either.
to address the obvious problem I already run a replace on the data so dates like May 1 become May 01.
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.
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.
- Humbulani Mutasah@hwmutasah: May you please help with formatting time to hh.mm.ss for the use of =LEFT(##,2) and =RIGHT(##,5).
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.
Assuming the day/month order is valid with your computer settings it is only the explicit commas that cause trouble, so
= LET( cleaned, SUBSTITUTE(text, ",", ""), DATEVALUE(cleaned) + TIMEVALUE(cleaned) )should return a valid datetime to be formatted any way you choose. The defined name 'text' refers to the entire range of text dates to be converted.
