Adding days to Current Week

[ZoeM]

Hi Community.

First time user here. I am developing a workflow and for the life of me I am stomped on how to add days, specifically 7, 14, and 28 days to the current week. 

Not sure if I will need to develop the current week field first, then add the days?

My current formula is:

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),7,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "One Week" ELSE

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),14,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "Two Week" ELSE

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),28,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "Four Week" ELSE

IF [Start_Date]<=DateTimeToday() and IsNull([Decl Dates]) THEN "In-Process" ELSE

"Way Ahead" ENDIF ENDIF ENDIF ENDIF

This works like a charm, but its based on today() and I need it to be based on current week ending Friday. I saw a blog on how to create an aggregated week and not sure whether that would be beneficial as a first step?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Summarize-data-by-the-week/td-p/6002

Any help provided will be greatly appreciated!

[danrh · Accepted solution]

Toss the following into a Formula tool as a new field and then use it instead of DateTimeToday():

DateTimeAdd(DateTimeToday(),
Switch(DateTimeFormat(DateTimeToday(),'%a'),0,
	'Sat',6,
	'Sun',5,
	'Mon',4,
	'Tue',3,
	'Wed',2,
	'Thu',1)
,'day')

You could put it all into one formula, but it would kind of long and ugly, so my preference would be to split it into two.

[ZoeM]

Thanks DanrH

That worked like a charm. It gives me next weeks count and I will test on Monday to see if I do get consistency as the week turns.

Much Obliged sir.