Find Latest Weekday and Previous Weekday

Posted on
November 6, 2018

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 and Weekday

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.

Posted on
November 6, 2018
in
Tips & Tricks
category

Ready to Speak Sigma?