Converting non-standard date format

[Median]

Hi all,

Another date format problem..

My data arrives in 3 columns (A,B,C) (Month Day, Year, Time) & I'm trying to convert it into "DD/MM/YYYY HH:MM" (Col D).

Should be simple, but I keep getting value errors & datevalue throws up strange results

Thanks
Mdn

[alansidman]

Power Query Solution

Please Login or Register  to view this content.

[Median]

Hi Alan, cheers for the solution, but how do I apply the code? I'm a total noob when it comes to power query (never used it; quickly reading about it now!).

I pasted it into the advanced editor, but received this error:

Attachment 764414

[FlameRetired]

If you are interested in a formula solution please try this in D2 and copy down. You will need to Custom format for date / time as m/d/yyyy h:mm

Please Login or Register  to view this content.

or to be on the safe side

Please Login or Register  to view this content.

and custom format as before.

I edited before refreshing and missed Mr. Shorty's intercept. Thank you Mr. Shorty.

[MrShorty]

This formula based solution worked for me:

1) Split the date column into month and day columns. You can use Text to columns for this with space as the delimiter, skip the first (blank) column, and have the destination be G2 (so it doesn't overwrite any of the source data).
2) Enter a table of month names. Assuming your data will always use a 3 character abbreviation for the month name, the list would be Jan, Feb, Mar,...,Nov,Dec.
3) Use a MATCH() function on this list to get the month number and put the day, month number, year into a DATE() function to get the date serial number =DATE(B2,MATCH(G2,$M$1:$M$12,0),H2
4) Use a TIMEVALUE() function to get the serial number for the time, and add it to step (3) =DATE(....)+TIMEVALUE(C2). Copy/paste/fill down as far as needed.

[MrShorty]

Expanding on Flameretired's solution (which will always assume the current year -- which is fine if all dates will be 2022 dates), this also worked for me:

1) My computer's OS regional settings expect a long date format of mmm d, yyyy, so I concatenate the date and year columns into something the computer can recognize A2&", "&B2
2) Put that in a DATEVALUE() function =DATEVALUE(A2&", "&B2)
3) As before, add the TIMEVALUE() to get the full date/time serial number =DATEVALUE(A2&", "&B2)+TIMEVALUE(C2)

The main downside I see to the DATEVALUE() function, and why I usually prefer the DATE() function is that the DATEVALUE() function relies on your computer's regional OS settings in order to interpret the input text. It is sometimes difficult -- especially if this needs to work across multiple computers that may have different regional settings -- to piece together a reliable input text for the DATEVALUE() function that will always work. Going to the extra effort to get year, month, and day numbers from the input text is that you can then put together a DATE() function that will consistently and reliably convert the text to serial number.

[Median]

Lovely stuff lads

Many thanks

[FlameRetired]

You are welcome. Thank you for the feedback and marking your thread Solved.