Find Latest Weekday and Previous Weekday

Current Weekday

Returns the Date of the current or most recent weekday (Monday through Friday). If the input date is a Monday, the function returns the input date. If the input date is a Sunday, the function returns the date of the Friday immediately previous.

DateAdd(“day”, (If(Weekday([DATE]) = 7, -1, Weekday([DATE]) = 1, -2, 0)), [DATE]) 

Previous Weekday

Returns the Date of the previous weekday (Monday through Friday). If the input date is a Monday, the function returns the date of the previous Friday.

DateAdd(“day”, (If(Weekday([DATE]) = 1, -2, Weekday([DATE]) = 2, -3, -1)), [DATE])

Explanation

To the current or previous weekday, we use 3 functions: DateAdd, If, and Weekday. The full function to find the current weekday is:

DateAdd(“day”, (If(Weekday([DATE]) = 7, -1, Weekday([DATE]) = 1, -2, 0)), [DATE])

That’s a doozy of an equation. Let’s unpack it.

DateAdd

DateAdd adds time to a date. The set up for the function is DateAdd(<unit>,<amount>,<date>). Our Unit is “day”, the amount is add is our If statement, and the date column is [Date]. 

DateAdd(“day”, (If Weekday([DATE]) = 7, -1, Weekday([DATE]) = 1, -2, 0), [DATE])

If returns a value based on whether logical conditions are met. The setup for the function is condition value pairs, which can be followed by an else statement. Our function has 2 condition values pairs, with an else statement. The full setup is:

If(<condition 1>, <value 1>, <condition 2>, <value 2>,<else>)

If(Weekday([DATE]) = 7, -1, Weekday([DATE]) = 1, -2, 0))

Both conditions use the Weekday function. Weekday returns the day of week for an input date, represented as the numbers 1 through 7, with 1 being Sunday and 7 being Saturday.

Condition 1 is Weekday([DATE]) = 7 which says to check if the day of week is 7 (aka Saturday). The value paired with condition 1 is -1. The condition value pair then says “If the Date is a Saturday, return -1”. This value feeds back into the DateAdd function, so that it subtracts one day from the input Date. Every time the date in the [DATE] column is a Saturday, the Friday before is returned.

Condition 2 does the same thing as Condition 1, but for Sundays. The condition value pair states Weekday([DATE]) = 1, -2 which says “if the Date is a a Sunday, return -2”. Subtracting two days from a Sunday gets you back to Friday.

The else condition at the end of the If statement handles all other cases. The If function returns the value 0 for all dates that aren’t a Saturday or Sunday. This then feeds the 0 into the DateAdd function, making no change to the date.