Changing Posting Date & Dynamic Date

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

Book1.xlsx

Workflow

Datasets

Developer

Date Time

Database Connection

Accepted answers

DataNath

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

All comments

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

Dynamic last of month.yxmd

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

New Workflow.yxmd

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280