Solved

Format Date Field to MMDDYY in A Generic Inquiry

  • 16 June 2022
  • 4 replies
  • 735 views
alexk95
Rank
Userlevel 1

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
Alex K

4 replies

Naveen Boga
Rank
Userlevel 7
Badge +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])))

 

 

Naveen B
alexk95
Rank
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 ……

 

Alex K
T
Rank
Userlevel 2
Badge

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

 

T
Rank

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

=Concat(PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'), Substring(CStr(Year([APPayment.DocDate])),3,2))

 

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.

=Concat(PadLeft(CStr(YEAR([APPayment.DocDate])), 4, '0'), PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'))

Reply


Terms & ConditionsCookie settings
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 — 2023  Acumatica, Inc. All rights reserved