Column with a specific date

[MKM]

Hi everyone, 

I'm trying to convert an data file that's normally made using multiple Excel sheets to be created via Alteryx. One of the columns in the Excel sheet is a "Week of" field that indicates the Sunday of the week that the data is covered since we look at the data week by week. Normally, the aggregation of the data happens about 10 days after that starting Sunday (i.e. 2/11/2018 is a Sunday so when it says 'week of 2/11/2018' it'll cover 2/11-2/17, but the data for that week wouldn't be pulled until 2/21/2018). Because it happens later, the use of something like a DATETIMENOW or something formula isn't possible. 

Is there a formula or a way to add the value of that Sunday? Right now, I'm just adding a formula and writing the date I want, but wasn't sure if there was a better way to do this

Thanks!

[john_miller9 · Accepted solution]

@MKM, 

You'll need to add a formula tool with the following expression.  Just swap out the [Date] for whatever your date field is.

DateTimeAdd([Date],
	Switch(DateTimeFormat([Date],'%a'),0,
		'Mon',-1,
		'Tue',-2,
		'Wed',-3,
		'Thu',-4,
		'Fri',-5,
		'Sat',-6),
	"days")