MYOB account numbers convert to date format

** Note that we recommend using the Castaway MYOB Integration rather than importing from MYOB via Excel. 

The Problem

A number of MYOB users have reported a problem with account number formats when exporting Trial Balance data into Excel. The account numbers from MYOB are converting into date format in Excel. This then causes issues with importing data into Castaway via the Excel Integration.

In the above, account number 1-2110 is displayed as Jan-10. Our challenge is then how to convert the date back into MYOB account number format, without resorting to manual entry.

The Solution

When we click on the cell containing Jan-10, it is displayed in the formula bar as 1/1/2110. Using Excel functions, we can then discover that this breaks down into 3 parts ... DAY=1, MONTH=1 and YEAR=2110.

With this knowledge, we can then reconstruct the MYOB account number 1-2110. If we assume Jan-10 is in cell C14, the formula is:  =MONTH(C14)&"-"&YEAR(C14).

The Next Problem

So, it seems we can simply add a new column and repeat this formula to fix our account numbers?

Not so fast!

You see, some of the account numbers in the list above are displaying correctly. Applying the formula to these cells yields a #VALUE! error. 

The trick here is to add a condition to the formula so it converts anything in a date format whilst not affecting correctly-displayed account numbers.

We can use the Excel TYPE function to detect the data type of a cell. The date cells are type 1 (General), whilst the correctly displayed account numbers are type 2 (Text). Wrapping this up with an IF function can then solve our problem.

So, the full formula is: =IF(TYPE(C14)=1,MONTH(C14)&"-"&YEAR(C14),C14). Copy this down the column and you will find your account numbers restored.