If today's date is between Start Date and End Date, then output the week number in Week column

[eyeana]

Hi,

I have 2 lists in Sharepoint: IT Wellbeing Weeks which has the week number, as well as the dates of the respective week and Wellbeing Tracking List v2, which contains WeekNumber, WeekNumber:StartDate and WeekNumber:EndDate which are lookup columns from IT Wellbeing Weeks list.

Through powerapps, i would like to create a new column called "Current Week" (or use the existing Week column), whereby if today's date is between WeekNumber:StartDate and WeekNumber:EndDate, then display the respective week number in Current Week/Week. For example: Week 1 - StartDate = 1/27/2020, EndDate = 2/3/2020, if date today is the 28th (1/28/2020), then output "1" in Week. 

How can i achieve this?

[EricLott]

You can use AddColumns() like this:

AddColumns(YourSPListName, "Current Week",And(StartDate <= Now(), EndDate >= Now()))

Here's the docs on AddColumns() for a better understanding of how it works.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up. 

[eyeana]

Hi @EricLott ,

What if I were to insert the week number in an existing column i already have on SP i.e. Week?

[EricLott]

Then you would use the Patch() function, giving it your data source, the row (or record) you want to update, then the column name and updated value.

[v-siky-msft]

@eyeana 

Do you mean first to check which week is today in, then patch the corresponding weeknumber (lookup field) into Wellbeing Tracking List v2?

Are the WeekNumber:StartDate and WeekNumber:EndDate are additional columns from weeknumber?

Set(VarItem, LookUp('IT Wellbeing Weeks', 'Start Date'<= Now() && 'End Date'>= Now())); 
Patch('Wellbeing Tracking List v2', Defaults('Wellbeing Tracking List v2'), {Title: "Test", WeekNumber: 
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: VarItem.ID
Value: VarItem.Week
}})

Note: The formula is to create a new record, so I add a required "Title" column.

Note: To patch a lookup column, you have to patch a record containing '@odata.type', Id and Value.

Sik