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.
Hi Mathetes,
Each of the responses you provided was spot on. Thank you very much.
I have taken some time to model some variations on what you provided and now better understand the formulae syntax.
Having also taken another look at the dashboard requirements to ensure they are satisfied I note there are a couple of permutations that still need addressing. Given that you enjoyed the initial challenge (smiley face) if you were up for a further challenge on the same theme (KPIs requiring twice-monthly updates) then your thoughts on these would also be very much appreciated:
- A single formula to address a combination of the ‘15th of month’ and ‘end of month’ possibilities. E.g.
- if the input date was, say, 7-Oct-21 the response would be 15-Oct-21; and,
- if the input date was, say, 19-Oct-21 the response would be 31-Oct-21.
- A single formula to address a KPI where an update is required every two weeks. E.g.
- A responses is required every other Friday commencing 8-Oct-21
- if the input date was, say, 7-Oct-21 the response would be 8-Oct-21; and,
- if the input date was, say, 11-Oct-21 the response would be 22-Oct-21.
Best regards.
That was indeed more fun. I got to use the QUOTIENT function for the first time. There are comments in the attached spreadsheet, which includes the formulas I created for your first inquiry, as well as these two new ones. I think I understood your specs, but in any event this should serve to help you modify to your specific needs. Let me know if you have more wrinkles to work with.
I highlighted the two new formulas by using blue bold text in the adjacent cells, where there's a brief label of what it does.
- Hi John,
Apologies for not responding before now but I have had to focus my time on other matters.
The bi-weekly formulae both work well. Thank you. Now I see your logical I recognise that FLOOR.MATH could also have used.
With respect to ‘15th of month and end of month’ I am not using the latest version of Excel on either of my laptops & therefore the formula including IFS will not work for me unless there is an alternate formula?
Many thanks again, & best regards.
- A single formula to address a combination of the ‘15th of month’ and ‘end of month’ possibilities. E.g.