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.

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.

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"""&")

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)

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)

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

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"

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.