Forum Discussion
Formula including future specific days/dates
This one was fun. Had to do some playing around to get all the answers.
Let's assume the last entered date is in cell C35.
The formula to determine the next Friday is:
=C35+CHOOSE(WEEKDAY(C35),5,4,3,2,1,7,6)
The formula to determine the next end of month is:
=IF(C35=EOMONTH(C35,0),EOMONTH(C35,1),EOMONTH(C35,0))
The formula to determine the next 15th of a month is
=IF(DAY(C35)<15,
DATE(YEAR(C35),MONTH(C35),15),
DATE(YEAR(C35),MONTH(C35)+1,15))
(and yes, those last two work across year boundaries as well)
Let me know if you want the formulas explained. You might have fun parsing them yourself, but if you get stumped, I'd be happy to explain how they work.
Hi Mathetes, thanks for your note.
In each case it is the very next Friday/15th/month-end, so if the update was done on Wednesday 8-Sept, the three formulae would report 2, 7 and 22 respectively.
This one was fun. Had to do some playing around to get all the answers.
Let's assume the last entered date is in cell C35.
The formula to determine the next Friday is:
=C35+CHOOSE(WEEKDAY(C35),5,4,3,2,1,7,6)
The formula to determine the next end of month is:
=IF(C35=EOMONTH(C35,0),EOMONTH(C35,1),EOMONTH(C35,0))
The formula to determine the next 15th of a month is
=IF(DAY(C35)<15,
DATE(YEAR(C35),MONTH(C35),15),
DATE(YEAR(C35),MONTH(C35)+1,15))
(and yes, those last two work across year boundaries as well)
Let me know if you want the formulas explained. You might have fun parsing them yourself, but if you get stumped, I'd be happy to explain how they work.