Help with rolling dates and weeks

[Julie_Clarke]

Hi,

I have the following data, which I need to replicate/generate from Alteryx:

Basically I need help on how I generate the week columns using the start and end dates.

In excel the calculations are as follows:

'this week' is: =IF(A19<TODAY(),F19,"")

WK1: =IF(A5<TODAY()+14,IF(B5>TODAY()+7,F5,""),"")

WK2: =IF(A5<TODAY()+21,IF(B5>TODAY()+14,F5,""),"")

WK3: =IF(A5<TODAY()+28,IF(B5>TODAY()+21,F5,""),"")

and so on.

Any ideas of suggestion gratefully received.

[ShankerV · Accepted solution]

@Julie_Clarke 

Please use the below formula.

WK1: =IF(A5<TODAY()+14,IF(B5>TODAY()+7,F5,""),"")

IF [A5]<datetimeadd(DateTimeToday(),14"days")
AND [B5]>datetimeadd((DateTimeToday(),7"days")
THEN [A5]
ELSE ""
ENDIF

Many thanks

Shanker V

[binuacs · Accepted solution]

@Julie_Clarke updated the formula, you can replicate the WK2 and WK3 from WK1

'this week'  -  IFF([start date] < DateTimeToday(),[resource per week],"")

WK1:  -- IIF([start date] < DateTimeAdd(DateTimeToday,14,'day'),IIF([finish date] > DateTimeAdd(DateTimeToday,7,'day'),[resource per week],""),"")

[ShankerV]

Hi @Julie_Clarke 

IF(A19<TODAY(),F19,"")

Use the below formula,

IF ([A19]<datetimetoday())

THEN [F19]

ELSE ""

ENDIF

Using this formula will bring all the dates < today hence, I will suggest to use rephrase the formula for 7 days.

Many thanks

Shanker V