Date formula help needed :(

[Bennets04]

Hey all,

I'm needing some help with a custom column formula. I have a DATE column which I want to add a column which takes that date and will return "LWE" (Last weekend) then "LWE-1" (Last weekend -1) depending on if the date is a Friday to Sunday.

Please find the Example below:

I have been able to do the Week column with the following formula:
if Date.IsInPreviousNWeeks(Date.AddDays([Date],0),1) = true then "LW" else if Date.IsInPreviousNWeeks(Date.AddDays([Date],7),1) = true then "LW-1" else if Date.IsInPreviousNWeeks(Date.AddDays([Date],14),1) = true then "LW-2" else if Date.IsInPreviousNWeeks(Date.AddDays([Date],21),1) = true then "LW-3" else if Date.IsInPreviousNWeeks(Date.AddDays([Date],28),1) = true then "LW-4" else null

But I just can't seem to work out how I can use a similar formula but to bring back whether the date is a weekend date.

Many thanks
Steve

 

[sandy666]

maybe

if Date.DayOfWeek([Date]) = 5 or Date.DayOfWeek([Date]) = 6 then Date.DayOfWeekName([Date]) else null

it's not clear what you want to return: day name, date from [Date] column or value from the [Week] column
replace blue to column what you want

 

[Bennets04]

maybe

if Date.DayOfWeek([Date]) = 5 or Date.DayOfWeek([Date]) = 6 then Date.DayOfWeekName([Date]) else null

it's not clear what you want to return: day name, date from [Date] column or value from the [Week] column
replace blue to column what you want

Thank you for this. Apologies so what I want to return is Text which can be preset... I want the formula to look at the DATE then tell me that date was either LWE (Last Weekend Friday-Sunday) or if the date was LWE-1, (the weekend 2 weeks prior) or LWE-2 (the weekend 3 weeks prior) and so on for 5 weeks.

Hope that helps
Cheers

 

[JustynaMK]

I suggest adding two new columns:
1) Date > Age (divide it by 7 and round the number down)
2) Date > Day of Week (where 4, 5, 6 are Fri, Sat, Sun)

From this point, it should be easy to create a Conditional Column which returns "Round Down" column if the day is Fri, Sat, Sun; otherwise return null. Replace nulls with blanks and add "LWE-" prefix if needed.

Hope it will get you started