Changing Posting Date & Dynamic Date

[Tid14]

Hi,

In the file attached I want to workout with something in which, the data remains as it is just the Posting Date and Document Date changes every month.

For eg- For May its showing  

5/31/2022

So when I'll run the data for June it changes to "Last date of June" i.e 6/30/2022.

Thanks in Advance

[DataNath]

@Tid14 I can't download your sample yet as it's still doing a virus scan. However, if you want this to always just be dynamic to the end of the current month (when you run the flow), you can just use the 'lastofmonth' DateTimeTrim(), as so:

DateTimeTrim(DateTimeNow(),'lastofmonth')

If you're precious about the format being mm/dd/yyyy then you'll have to wrap it in the DateTimeFormat() function and change the output data type to a string like so:

DateTimeFormat(DateTimeTrim(DateTimeNow(),'lastofmonth'),'%m/%d/%Y')

[IraWatt]

Hey @Tid14,

You can use some of the date time functions to achieve this:

DateTimeTrim(DateTimeNow(),"lastofmonth")

This will always return the end of the month whenever you run it.

Any questions or issues please ask :)
HTH!
Ira

[Luke_C]

Hi @Tid14 

You could also just use the 'DateTimeLastOfMonth()' function to do the same thing as the others suggested.

https://help.alteryx.com/20221/designer/datetime-functions

[DataNath]

@Luke_C I've been on that page so many times and must've skimmed that - never noticed it as a standalone function! Cheers!

[IraWatt]

Thanks @Luke_C 😅

[Tid14]

If its coming virus scan, I can paste the data for your reference. I want Dates to be in the column next to Document date and posting date. Do i need to use a Cross Tab first and then formula or is there any other way too?

Thanks!

COMPANY	XYZ
DOCUMENT DATE	5/31/2022
POSTING DATE	5/31/2022
DOCUMENT TYPE	PP
DOCUMENT REFERENCE	1943
DOCUMENT TEXT	Reclass PQQ loss
CURRENCY	XXXX
EXCHANGE RATE
LEDGER GROUP
SPECIAL PERIOD
MJE TOTAL	0.00

[DataNath · Accepted solution]

No need, you can just do a check as part of your Formula expression to see if it's one of those rows:

if [COMPANY] in ('DOCUMENT DATE','POSTING DATE') THEN DateTimeFormat(DateTimeLastOfMonth(),'%m/%d/%Y') else [XYZ] endif

If you're fussy about the leading 0 then you can add the TrimLeft() function too:

if [COMPANY] in ('DOCUMENT DATE','POSTING DATE') THEN Trimleft(DateTimeFormat(DateTimeLastOfMonth(),'%m/%d/%Y'),'0') else [XYZ] endif

[Tid14]

That was really helpful

Thanks Cheers 👍

[Tid14]

Thanks Cheers :)