Find latest weekday and previous weekday

Date of the current or most recent weekday

The following formula 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]) 

Date of the previous weekday

The following formula 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])

Functions used in the current or previous weekday formula

To get the current or previous weekday, the formula uses three functions: DateAdd, If, and Weekday.

To reiterate, the full function to find the current weekday is:

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

DateAdd

DateAdd adds time to a date. The syntax is DateAdd(<unit>,<amount>,<date>). Our Unit is “day”, the amount is add is the 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. The function has 2 condition values pairs, with an else statement. The full syntax is:

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 by 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 (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.