At a high level, this means we need to subtract some number of days from the given date.
For example, if the given date is a Monday, we need to subtract 1 day.
If the given date is a Tuesday, we need to subtract 2 days, and so on.
Note: the formulas below use simple math to adjust the date.
This works becauseExcel dates are large serial numbers.
WEEKDAY solution
TheWEEKDAY functionaccepts a date and returns a number between 1-7 representing the day of week.
Subtracting 7 from the original date in B5 will return the previous Sunday.
The formula works the same way for each day of the week.
This isn’t always desired behavior.
Sometimes, the goal is to leave the date alone if it’s already the right day of week.
One way to accomplish this is with theCHOOSE function.
The CHOOSE function is used to select values by numeric position.
The values that follow WEEKDAY are the actual rollback numbers.
In that case, CHOOSE will return zero and the formula itself will return the original date unchanged.
The underlying date is unchanged.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable.
you’re able to use the WEEKDAY function inside other formulas to check the day of week.
The values provided to CHOOSE can include references.
