Solved

# Format Date Field to MMDDYY in A Generic Inquiry

• 4 replies
• 1111 views

Userlevel 1
• Freshman II
• 7 replies

Hello,

I am trying to format a date field on the results grid on a GI to be MMDDYY with no dashes or slashes I.e.

06/01/2022  should be formatted 060122, is there a way to convert with formatting or function?

Any help would be greatly appreciate it.

icon

Best answer by alexk95 16 June 2022, 22:10

View original

### 4 replies

Here is another option using PadLeft. It simplifies the formula slightly.

Breaking this down into the different parts:

=Concat(  -- Combines the various strings together

PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), -- MONTH: Extracts the month from DocDate, CSTR: Converts it to a string value, PADLEFT: Ensures it is 2 characters wide with ‘0’ placed at the left to accomplish this

PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'), -- DAY: Extracts the day from DocDate, CSTR: Converts it to a string value, PADLEFT: Ensures it is 2 characters wide with ‘0’ placed at the left to accomplish this

Substring(CStr(Year([APPayment.DocDate])),3,2) -- YEAR: Extracts the year from DocDate, CSTR: Converts it to a string value, SUBSTRING: Extracts the characters starting at position 3, 2 characters

)

For those looking to follow the ISO 8601 standard, here is a formula for that.

Userlevel 3

Thanks for this.  I have to create the same thing for us.  Our difference is ours requires a / between the dates.  So mine is:

Returns:  05/04/2023

``=Concat(IIf(Month([APPayment.DocDate]) < 10, '0'+CStr(Month([APPayment.DocDate])), CStr(Month([APPayment.DocDate]))) + '/' + IIf(Day([APPayment.DocDate]) < 10, '0'+CStr(Day([APPayment.DocDate])), CStr(Day([APPayment.DocDate]))) + '/' + CStr(Year([APPayment.DocDate]) ))``

Userlevel 1

Naveen,

Thank you so much for your prompt reply! , just what I needed , it was almost there with the exeption that I needed something like this , for 06/15/2022  I needed to display 061522 so I modified the statement slightly , I am posting this so it might help someone else in the future.

New revised statement

=Concat(IIf(Month([APPayment.DocDate]) < 10, '0'+CStr(Month([APPayment.DocDate])), CStr(Month([APPayment.DocDate]))) + IIf(Day([APPayment.DocDate]) < 10, '0'+CStr(Day([APPayment.DocDate])), CStr(Day([APPayment.DocDate]))) + Substring(CStr(Year([APPayment.DocDate])),3,2) )

It returns values as below ……

Userlevel 7
+17

Hi @alexk95  We can do that with the below formula. You can replace the SOOrder.OrderDate with APPayment.DocDate.

= Concat( CStr(Month([SOOrder.OrderDate])) , CStr(Day( [SOOrder.OrderDate])) ,CStr(Year([SOOrder.OrderDate])))