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.
Apologies for not previously providing sufficient clarity. Whilst my previous note may still be useful for general context I suggest it is set aside, and let me try and clarify the essential elements:
whatever day a user updates a KPI result the date of the update is input to a data collection worksheet.
There is a need to develop (three?) formulae that calculate from the update date:
(i) the number of days to the following Friday;
(ii) the number of days to the next 15th of a month; and,
(iii) the number of days to the next end of a month.
With the above formulae providing correct quantitive results I believe I will then be able to develop the corresponding formulae that will report the required narrative statements.
Any assistance you can provide in developing the required formulae at (i) to (iii) above would be most appreciated. Thank you.
I'm on vacation at present, only sporadically checking these boards. These are intriguing formulas you're seeking and I"m looking forward to working on them, confident I'll be able to resolve (although it's going to take some playing around)....but it could easily be a week before I can devote the time. In the meantime, and to help make sure that if somebody else jumps in that we're addressing exactly what you need, let me ask just a couple more clarifying/confirming questions.
Let's assume the date of the last entry is in cell A1, for the purposes of discussion:
(i) the number of days to the following Friday;
If the date in A1 is that of a Thursday, do you mean the very next day, or the Friday of the following week?
(ii) the number of days to the next 15th of a month;
If the date in A1 is the 14th, do you mean the very next day, or the 15th of the following month?
(iii) the number of days to the next end of a month.
If the date in A1 is the 27th of the month, do you mean the end of the same month, or the end of the following month?
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.
- A single formula to address a combination of the ‘15th of month’ and ‘end of month’ possibilities. E.g.