Date time formula

[henrygeorge]

Hi Alteryx team,
Please help me in framing a formula in such a way that all rows are with the previous days date eg if today is 18 Nov it results in 17 Nov.. But however 17 Nov and 16 Nov is a holiday so we just take the 15 Nov as the previous date and show it as a result. ie ignoring the weekends. How can this be done?

[benakesh]

Hi @henrygeorge ,

This  is the formula  for previous workday ( Mon - Fri)  for  a  field : date .  You replace  'date'  with  current date or any other date in your  data .

For Sunday and Monday previous day is Friday . For all other days it is previous day .

if DateTimeFormat(date,'%a') = 'Sun' then DateTimeAdd(date,-2,'days')
elseif DateTimeFormat(date,'%a') = 'Mon' then DateTimeAdd(date,-3,'days')
else DateTimeAdd(date,-1,'days')
endif

[henrygeorge]

@benakesh can you please send your alteryx working file?

[Jonathan-Sherman · Accepted solution]

Hi @henrygeorge,

I've done it slightly differently, just using one formula:

IF DateTimeFormat([Date],'%a') = 'Mon'
THEN DateTimeAdd([Date],-3,'day')
ELSEIF DateTimeFormat([Date],'%a') = 'Sun'
THEN DateTimeAdd([Date],-2,'day')
ELSE DateTimeAdd([Date],-1,'day')
ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan