Another date solution

Formula to the rescue

Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.

The latest requested format is shown below.

The request was to create a single formula that would convert the text date/time in an Excel date/time.

The tricky bit here is the fact that the date and the hour can both be one or two digits – so the length of the string varies.

I broke the requirement into two parts and then combined the parts to end up with a very long final solution formula.

A date-time fact that you need to know in Excel. A date is a whole number and time is a decimal (fraction of 24). A date has an underlying number 1 equals 1/1/1900 and every day since has its own sequential number. 43101 is 1 January 2018. Noon on that day is 43101.5 and 6AM on that day is 43101.25 and 6PM is 43101.75.

A file with the all the formulas is at the bottom of the post.

Extract the date

First we will extract the date. The formula in cell B2 is

=DATE(MID(A2,7,5),MONTH(1&LEFT(A2,3)),MID(A2,5,2))

The DATE function builds the date using Year number, Month number and the Day number, in that order.

The DATE function is very accommodating as it automatically converts text numbers into real numbers and it ignores leading and trailing spaces.

Year

The year either starts in character position 7 or 8. The following MID function extracts the year.

MID(A2,7,5)

Given that the year is preceded and followed by a space we can start the date in position 7 and extract 5 characters.

The MID function extracts from the middle of a text string. You provide the cell to extract from, the starting character position and then the number of characters to extract. The starting character position is included in the extracted text.

The above MID function will either include the preceding space or the trailing space as well as the year.

The DATE function is helpful and ignores the space (leading or trailing) and treats the text number as a year number.

Month

My most popular blog post is on converting a text month name into a month number – we can use that technique here.

MONTH(1&LEFT(A2,3))

If you type 1Sep into a cell Excel with convert it into a date. This is what I am creating with

1&LEFT(A2,3)

1 is combined with the first 3 letters of the text date to create 1Sep.

The MONTH function returns the number for September.

Day

The day number is either one or two digits long. If we assume two digits then luckily the DATE function will ignore the space.

MID(A2,5,2)

That’s how we extract the three date components from the text date.

Time

This formula must handle the different positions for the time part of the string.

The TIMEVALUE function converts a text time into a fraction – which is how time is stored in Excel. Dates are whole number and time is a decimal (fraction of 24). 0.25 is 6AM, 0.5 is noon and 0.75 is 6PM.

The formula for cell C2 is

=TIMEVALUE(MID(A2,SEARCH(":",A2)-2,5)&" "&RIGHT(A2,2))

First issue – the text required for the TIMEVALUE function has a space between the time and the AM/PM. Our time doesn’t have a space.

& ” “&RIGHT(A2,2))

This part of the formula handles the space before the AM/PM.

The next problem we face is we don’t know where the time starts because the day could have one or two digits.

MID(A2,SEARCH(“:”,A2)-2,5)

Whilst we don’t know where the time starts we can figure out where the colon is using the SEARCH function. The SEARCH function returns the character position number of the searched for text. Once we have the position of the colon we can deduct 2 from it to get the start of the time, assuming a two digit hour. If it is a one digit hour we get a leading space, but the TIMEVALUE function seems to handle that extra space ok. The length of a two digit hour time is 5 characters including the colon.

Date and time

To get the data AND the time you just add the two formulas together.

The formula for cell D2 is

=DATE(MID(A2,7,5),MONTH(1&LEFT(A2,3)),TRIM(MID(A2,5,2)))+TIMEVALUE(MID(A2,SEARCH(":",A2)-2,5)&" "&RIGHT(A2,2))

Its quite long, but it does the job.

Thanks to Mike for asking the question.

Example Excel File

 

 

 

 

 

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply to anirban ghosh Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

9 thoughts on “Another date solution

  1. Hi
    I’ve just had to develop a formula which I based on your date solution – for which I thank you.
    At the moment I don’t know the exact format i.e. if it is always a two digit day format or not. All I have so far is a date “April 22, 2020″ so at least I know it is the full month I’m working with which pushes the day (1 or 2 digits) all over the place. So basically the month is as you have it MONTH(1&LEFT(G1,3)) the year, in the original, is far from the simple version I have here of RIGHT(G1,4)!! The day of the month with the month length of 3 to 9 characters was the challenge! The only way I could think of doing it was to find the position of the first comma and subtract from that the position of the first space as in:-
    =DATE(RIGHT(G1,4),MONTH(1&LEFT(G1,3)),MID(G1,FIND(” “,MID(” “,G1,1)),FIND(“, “,G1,1)-FIND(” “,G1,1)))
    Well it works so it’s got to be good enough 🙂
    Oh what fun we have.
    best
    Tony

    • Hi Tony
      You can assume the day will have 2 digits even if it doesn’t. Excel can handle a space and a digit and it will treat it like a number. The DATE function is really forgiving when it comes to converting text to numbers. Remember the RIGHT and MID functions both return text values.

      Try this

      =DATE(RIGHT(G1,4),MONTH(1&LEFT(G1,3)),MID(G1,FIND(“,”,G1)-2,2))

      Regards
      Neale

  2. Hi Tony,

    Thank you for this solution.I want your help in more date format,could you please help.
    I want to convert ‘May/27/21 8:07 AM’ date format to 5/27/2021.

    TIA,
    Anirban

    • That’s tricky.
      If the date is in A1.
      Place this formula in B1.
      =LEFT(A1,SEARCH(” “,A1)-1)
      then in C1 use
      =DATE(RIGHT(B1,2)+2000,MONTH(1&LEFT(B1,SEARCH(“/”,B1)-1)),MID(B1,SEARCH(“/”,B1)+1,2))
      It assumes all dates are after 2000.
      hope that helps.

  3. Hi.
    How can I convert 30-JUN-21 01.45.18.488173000 PM to 13:45:18 (24 hr format)?

    How can I convert if the date time string is in AM but can be converted to 24 hr format?

    I am trying to build formula that can convert timestring to time with AM and PM.

  4. Hi.
    How can I convert 30-JUN-21 01.45.18.488173000 PM to 13:45:18 (24 hr format)?

    How can I convert if the date time string is in AM but can be converted to 24 hr format?

    I am trying to build formula that can convert timestring to time with AM and PM.

    Thank you already.