Forum Discussion
Trying to get weekend dates that are typed in to round to nearest workday
Trying to get Saturday dates to round to Friday and Sunday Dates to round to Monday. I have formulas to do one or the other but can't figure out how to place both formulas for single cell.
=IF(WEEKDAY(A5)=7,A5-1,A5)
=IF(WEEKDAY(A5)=1,A5+1,A5)
Thanks in advance. Haven't used excel much!
8 Replies
- Still another CHOICE would be:
=A5+
CHOOSE(WEEKDAY(A5),
1,0,0,0,0,0,-1) - My alternative version is:
=A5+
(WEEKDAY(A5)=1)-
(WEEKDAY(A5)=7) Just for the hell of it, I defined a named formula 'adjustment' to refer to
= INDEX( {1,0,0,0,0,0,-1}, WEEKDAY(date) )The worksheet formula for the adjusted date is then
= date + adjustment
If you prefer your weekdays to start on a Monday then the first formula becomes
= INDEX( {0,0,0,0,0,-1,1}, WEEKDAY(date, 2) )
- It occurred to me that there's another function that is perfectly suited for your application. Try this formula: =CHOOSE(WEEKDAY(A5),2,2,3,4,5,6,6)
As the name of the function implies, this "chooses" what to put in the cell based on the value of A5, picking the one that is in the sequential order corresponding to the value of (in this case) WEEKDAY(A5).
Very useful function to know about whenever you have a simple set of alternatives that are easily ordered in sequence by integer numbers. - =IF(WEEKDAY(A5)=7,A5-1,IF(WEEKDAY(A5)=1,A5+1,A5))
the logic here is referred to as "nesting" in that the second IF is "nested" as the second alternative in the first or "outer" IF statement.
A simple IF function is read as IF([condition is met] THEN [do this] ELSE [do that])
And all you're doing is putting another IF function into the ELSE place, and this latter instance has as its ELSE, in your case, "Leave A5 untouched."
make sense?
