Solved

US date format to AUD

  • 15 December 2021
  • 16 replies
  • 165 views

Userlevel 2
Badge

Hello, @vkumar , @Naveen B 

 

I have an American date format in an excel file that needs to be uploaded into MYOB Adv.  The format is like so ‘20231231’ (i.e. YYYMMDD).  How can I convert this to an AUD format in the Import Scenario?

Regards,

Laura

icon

Best answer by Naveen Boga 16 December 2021, 13:17

View original

16 replies

Userlevel 7
Badge +12

Hi @lauracastagna  I don’t think direct conversion is available from US to AUD.

As a alternate, you can have a formula just like below and verify.

Hope this helps!

Day( Today() ) + '/' + Month( Today() ) + '/' + Year( Today() )

 

Userlevel 2
Badge

Hi @Naveen B ,

 

so where in the formula do i add the cell field the file picks up please?  

Userlevel 7
Badge +12

Yes @lauracastagna  that is correct!

In the above example, you can add the cell field in the place of “Today()”

 

 

Userlevel 2
Badge

HI @Naveen B ,

 

i have :-

=Day([BATCH_EXPIRY])+'/'+Month([BATCH_EXPIRY])+'/'+Year([BATCH_EXPIRY])

 

I have changed the field to data type = date in the Data provider but am still getting error:-

 

“An error has occurred while the Day(Identifier(BATCH_EXPIRY)) function was being executed:
'Type mismatch in the argument 1 of the Day() function: The argument must have the DateTime type.”

Userlevel 2
Badge

Hi @Naveen B ,

 

I’ve also tried this but it doesnt like the MID function:-

=Right([BATCH_EXPIRY],2)+'/'+MID([BATCH_EXPIRY],5,7)+'/'+Left([BATCH_EXPIRY],4)

Userlevel 7
Badge +12

Hi @lauracastagna  According to me below formula should work.

=Day([BATCH_EXPIRY])+'/'+Month([BATCH_EXPIRY])+'/'+Year([BATCH_EXPIRY])

 

Can you please let us know that have you updated manually at screen with this format and field is accepting this formatted value?

 

Userlevel 2
Badge

Hi @Naveen B ,

 

I manually entered the formula and validated it but its still not working.  Could it have something to do with the Import Scenario being of type CSV?  Still getting the following ERROR:-

 

An error has occurred while the Day(Identifier(BATCH_EXPIRY)) function was being executed:
'Type mismatch in the argument 1 of the Day() function: The argument must have the DateTime type.'

 

 

 

I did try changing the Data Type to Date but that didnt work either.

Userlevel 2
Badge

sample of file attached.

Userlevel 7
Badge +12

Hi @lauracastagna  Since it is NOT a date field you are getting that conversion issue.

Could you please try to convert to Date Type using below function and verify.

Conversion To Date: CDate(BATCH_EXPIRY)

 

=Day(CDate(BATCH_EXPIRY))+'/'+Month(CDate(BATCH_EXPIRY))+'/'+Year(CDate(BATCH_EXPIRY))

 

Hope this helps!

Userlevel 2
Badge

Hi @Naveen B ,

 

still not working.  I manually input the formula above and Validated it but still getting the following error:-

An error has occurred while the CDate(Identifier(BATCH_EXPIRY)) function was being executed:
'String was not recognized as a valid DateTime.'

 

Userlevel 2
Badge

@Naveen B ,

 

does it work for you ?

Userlevel 7
Badge +12

@lauracastagna  It is not working for me as well and getting same issue. Looking for alternative to achieve this requirement.

 

Userlevel 2
Badge

@Naveen B 

 

okay thanks.  I am trying other things too but not having luck either

Userlevel 7
Badge +12

Sure @lauracastagna  If I found anything will let you know

Userlevel 7
Badge +12

Hi @lauracastagna  Finally!! I found a workaround solution for this and hope this helps!

Since the BATCH_EXPIRTY date is in the format YYYYMMDD, I created a formula accordingly and it is working as expected.

Here is the formula: 

=CDate(Substring( [BATCH_EXPIRY], 4, 2) + '/' + Substring( [BATCH_EXPIRY], 6, 2) + '/' + Substring( [BATCH_EXPIRY], 0, 4))

 

 

 

Userlevel 2
Badge

Thank-you @Naveen B 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved