Parse date from long date string

unknown

There are several fields in Spreadsheet Designer that present a date as a long text string.  I've tried a number of ways to get the field to just present as a date but I end up having to use a multi-step approach with formulas to parse the date out of it.

The report I'm currently working on is pulling CreateDate from PayablesInvoice.  For example, the date is presented as "Saturday, 18 May 2019 03:45:50.00 AM".  It doesn't matter if I change the format of the field to any of the date formats.  I've tried using formulas date(), datevalue(), etc.

So far the only way I have been able to get the date from it is to use this approach:

Column 1: use MID() to find the year and then remove what comes after the year (note this gets tricky with multiple years)

example: =MID(A2,1,(FIND(2019,A2)+4))

 

column 2: use MID() to find the comma and remove what comes before the comma

example: =MID(B2,FIND(",",B2)+1,30)

 

column 3: use VALUE() to turn the result from column 2 into a date

example: =VALUE(C2)

 

I would love a better way to do this.  Thanks in advance

don-peterson

This may get you closer...



The PayablesInvoice business class has a derived field, DerivedCreateDate, that you can try. When I do it in Spreadsheet Designer returning both CreateDate, which is a TimeStamp field, and DerivedCreateDate, they are close. It looks like that if it is in the AM, the create dates match, if it is in the PM, it shows the next day.



Looking at the source LPL in the Rich Client LPL Viewer widget from the Library (Start|Library) you can see the definition of that derived create date field:



DerivedCreateDate is a DerivedField

type is Date

TimeStampInGMT.Value = CreateDate

return TimeStampInGMT.CorporateDate



Hope this helps some.



Don

0712050808501819.doc