VBA to insert IF formula to convert dates to DD/MM/YYYY

  • Hi there,


    I have dates in column B (DATE) which are in text format and the length of each cell (using the LEN(Text) function) can vary from 5, 21 and 22 as shown below.

    DATE Column C Character Length
    2/16/2010 12:00:00 AM   21
    12/20/1991 12:00:00 AM   22
    10/23/2019 12:00:00 AM   22
    6/26/1985 12:00:00 AM   21
    06/04/1987 0:00   5


    In cell C2 (Column C), I usually use the formula below and copy down to the last record.

    =IF(LEN(B2)=5,"=TEXT((LEFT((DATE(MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),7,4),LEFT((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),2),MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),4,2))),10)),"&"""DD/MM/YYYY"""&")", IF(LEN(B2)=21,"=MID((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),3,3)"&"&"&"""0"""&"&"&"LEFT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),1)"&"&"&"RIGHT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),5)", IF(LEN(B2)=22,"=MID((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),4,3)"&"&"&"LEFT("&""""&B2&""""&",2)"&"&"&"RIGHT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),5)")))

    I then use Excel's Text to Columns wizard to convert the dates to DD/MM/YYYY.


    I'm trying to create a macro to do the above but when I record the macro to just insert the formula in C2, it gives me a "Compile Error, Syntax Error". Below is code that is generated after recording.


    Range("C2").Select

    ActiveCell.FormulaR1C1 = _

    "=IF(LEN(RC[-1])=5,""=TEXT((LEFT((DATE(MID((TEXT(""&""""""""&RC[-1]&""""""""&"",""&""""""DD/MM/YYYY""""""&"")),7,4),LEFT((TEXT(""&""""""""&RC[-1]&""""""""&"",""&""""""DD/MM/YYYY""""""&"")),2),MID((TEXT(""&""""""""&RC[-1]&""""""""&"",""&""""""DD/MM/YYYY""""""&"")),4,2))),10)),""&""""""DD/MM/YYYY""""""&"")"", IF(LEN(RC[-1])=21,""=MID((LEFT(""&""""""""&RC[-1]&""""""""&""& _

    ""&"""""" """"""&"",""&""""""""&RC[-1]&""""""""&"",1)-1)),3,3)""&""&""&""""""0""""""&""&""&""LEFT((LEFT(""&""""""""&RC[-1]&""""""""&"",FIND(""&"""""" """"""&"",""&""""""""&RC[-1]&""""""""&"",1)-1)),1)""&""&""&""RIGHT((LEFT(""&""""""""&RC[-1]&""""""""&"",FIND(""&"""""" """"""&"",""&""""""""&RC[-1]&""""""""&"",1)-1)),5)"", IF(LEN(RC[-1])=22,""=MID((LEFT(""&""""""""&RC["& _

    """"&"",FIND(""&"""""" """"""&"",""&""""""""&RC[-1]&""""""""&"",1)-1)),4,3)""&""&""&""LEFT(""&""""""""&RC[-1]&""""""""&"",2)""&""&""&""RIGHT((LEFT(""&""""""""&RC[-1]&""""""""&"",FIND(""&"""""" """"""&"",""&""""""""&RC[-1]&""""""""&"",1)-1)),5)"")))"


    Any idea where the issue is? as i can't seem to locate it. I tried inserting line breaks "& _" but getting errors (i.e. "Compile error. Expected end of statement").


    Appreciate your help.:(

  • Instead of trying to reverse engineer your formula, suggest you explain in plain English what you want to happen. Additionally, you have shown us what you have. Now, mock up a solution so that we have a clear idea of what you want to happen.

  • Hi AlanSidman,


    I've attached a sample file which shows the formula in action but basically i just want to convert the dates in Column B (DATE) to the format dd/mm/yyyy in Column C.


    The formula checks the length of the text in each cell in Column B as they're not consistent and uses a specific formula based on the length. The LEFT,MID and RIGHT functions only rearrange the result to the desired format of dd/mm/yyyy.


    for e.g

    If the length of the text in cell B2 = 5, the date format is usually in 'mm/dd/yyyy hh:MM:ss AM' hence the formula i use to change the date to dd/mm/yyyy is:

    =TEXT((LEFT((DATE(MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),7,4),LEFT((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),2),MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),4,2))),10)),"&"""DD/MM/YYYY"""&")


    DATEExpected result
    06/04/1987 12:00:00 AM 04/06/1987



    If the length of the text in cell B2 = 21, the date format is m/dd/yyyy hh:MM:ss hence the formula used to change the date to dd/mm/yyyy is:=MID((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),3,3)"&"&"&"""0"""&"&"&"LEFT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),1)"&"&"&"RIGHT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),5)


    DATE Expected Result
    2/16/2010 12:00:00 AM 16/02/2010


    If the length of the text in cell B2 = 22, the date format is usually in 'mm/dd/yyyy hh:MM:ss AM' format hence the formula i use to change the date to dd/mm/yyyy is:

    =MID((LEFT("&""""&B3&""""&",FIND("&""" """&","&""""&B3&""""&",1)-1)),4,3)"&"&"&"LEFT("&""""&B3&""""&",2)"&"&"&"RIGHT((LEFT("&""""&B3&""""&",FIND("&""" """&","&""""&B3&""""&",1)-1)),5)



    DATE Expected Result
    12/20/1991 12:00:00 AM 20/12/1991


    Issue

    The recorded macro to insert the consolidated formula (Below) in C2 is producing a syntax error


    =IF(LEN(B2)=5,"=TEXT((LEFT((DATE(MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),7,4),LEFT((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),2),MID((TEXT("&""""&B2&""""&","&"""DD/MM/YYYY"""&")),4,2))),10)),"&"""DD/MM/YYYY"""&")", IF(LEN(B2)=21,"=MID((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),3,3)"&"&"&"""0"""&"&"&"LEFT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),1)"&"&"&"RIGHT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),5)", IF(LEN(B2)=22,"=MID((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),4,3)"&"&"&"LEFT("&""""&B2&""""&",2)"&"&"&"RIGHT((LEFT("&""""&B2&""""&",FIND("&""" """&","&""""&B2&""""&",1)-1)),5)")))


    Hope I've provided sufficient info.

    Book3.xlsx

  • Using Power Query/Get and Transform, here is the Mcode for solving your issue


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    4. #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    5. #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type with Locale", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    6. #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
    7. #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date.2", "Date.1", "Date.3"}),
    8. #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.2", type text}, {"Date.1", type text}, {"Date.3", type text}}, "en-US"),{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged")
    9. in
    10. #"Merged Columns"
  • Hi AlanSidman,


    I tried the Mcode you provided but it doesn't convert the dates to the desired "dd/mm/yyyy" format. Below is a snap shot of the result, which i ran against the same sample data i provided earlier.



    The error message being:

    For the first record

    "DataFormat.Error: We couldn't parse the input provided as a DateTime value. Details: 2/16/2010 12:00:00 AM"


    For the second record

    "DataFormat.Error: We couldn't parse the input provided as a DateTime value. Details: 12/20/1991 12:00:00 AM"


    Not sure if I've missed anything but do point it out, if i have.

  • ok i've changed the Mcode to "en-AU" but still getting errors. refer snap shot below.



    Is there anything else i need to change as a result of changing the locale?

    Here's the Mcode:


    let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),

    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-AU"),

    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type with Locale", {{"Date", type text}}, "en-AU"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date.2", "Date.1", "Date.3"}),

    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.2", type text}, {"Date.1", type text}, {"Date.3", type text}}, "en-AU"),{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged")

    in

    #"Merged Columns"

  • Tried a different method


    Left out step using Locale. See if that works for you.

  • ok tried it but still getting errors. Note that under the Applied Steps, the error seems to start from the Changed Type step (refer below). I noticed the same in the initial Mcode that includes the locale.