US date format to AUD

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() )

Hi @Naveen B ,

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

Yes @lauracastagna  that is correct!

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

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.”

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)

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?

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.

sample of file attached.

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!

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.'

@Naveen B ,

does it work for you ?

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

@Naveen B 

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

Sure @lauracastagna  If I found anything will let you know

Thank-you @Naveen B