Quote:
Originally Posted by Karen615
=TODAY()-1-(MOD(TODAY(),7)=2)*2
Thank you so much for your help. If you have a chance, would you please explain this formula in layman's terms?
|
Dates in Excel are stored as numbers, going back to 1 Jan 1901, and incrementing by 1 for each day.
When we divide a date by 7, the remainder tells us what day of the week it is. If the remainder is:
0 - the day is a Saturday
1 - the day is a Sunday
2 - the day is a Monday
3 - the day is a Tuesday
4 - the day is a Wednesday
5 - the day is a Thursday
6 - the day is a Friday
The MOD function returns the remainder of a number and its divisor. Hence, if the remainder returned by MOD function of a date divided by 7 is 2, the date is a Monday.
So:
TODAY()-1
returns yesterday
MOD(TODAY(),7)=2
is a True/False test for Monday and, if TRUE
-(MOD(TODAY(),7)=2)*2
deduct another 2 days from today.