Change sundays to mondays

[mattue84]

ok with referals they come in all hours of the day. but we only work M-F with a few hours on Sat. so all the refureals on Sunday really dont get worked till monday. So I need to beable to push the ones that fall on Sun to the next day. So I can push all sunday referals to 8am no problem, but i need to also push the date from 3/20/2022 to 3/21/2022.

I do have a colum that has the day ot eh week so I can easly filter to only the sunday referals. 

This is needs to be done in Power Pivot. i would try a If and statment but cant get it to work or maybe just bad formula.

= Table.AddColumn(#"Expanded Dates", "Custom", each if [Dates.Day of Wk]= "Sun" then [List Details.TIMESTAMP]+0/1/0 else [List Details.TIMESTAMP])

[v-jingzhang · Accepted solution]

Hi @mattue84 

What columns do you already have and what is the expected result?

According to the code you provided, it seems you want to add a column and add one day to dates falling on Sundays, right? If so, you can use Date.AddDays function. 

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then Date.AddDays([Date],1) else [Date])

Or use [Date]+#duration(1,0,0,0) to get the same result. #duration function

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then [Date]+#duration(1,0,0,0) else [Date])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@mattue84 , in the previous step, where you setup day of week you can use additional parameter to set start of week as monday

https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek

Date.DayOfWeek(#date(2011, 02, 21), Day.Monday)