Sep 29 2021 03:57 PM
A big ask of a Formula Expert please.
There is need to develop a dashboard that shows a narrative statement against each of a series of Key Performance Indicators. Data inputs are due each Friday and on the last calendar day of each month (however it is recognised that is not always possible for the updates to be done when due).
At any time the Dashboard is required to show against each KPI a relevant narrative statement e.g. ‘Updated today’; ‘Overdue’; ‘Update due next Friday’; ‘Update due on the 15th’; or, ‘Update due at the end of the month’.
I have made some progress in developing a formula (below), however I am stumped as to how to finalise it to accommodate when updates are required: (i) ‘next’ Friday; (ii) the 15th of the month; and, (iii) the end of the month.
=IF(AND(C2=0),"Updated today",IF(AND(C2>=-99,C2<=-1),"Overdue"," "))
Any help would be very much appreciated. Many thanks in advance.
Sep 30 2021 09:58 AM
Cell C2 is obviously key in that set of conditionals. So some questions:
Is it possible for you to post a copy of the actual spreadsheet, or at least a mockup of the dashboard as it currently exists in development. Don't include actual confidential or proprietary data, of course, but the more you could share of the actual (or a mockup), the more we could give a recommendation that relates to your actual situation.
Sep 30 2021 04:02 PM
Oct 01 2021 09:00 PM
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?
Oct 03 2021 12:01 PM
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.
Oct 05 2021 05:18 AM - edited Oct 05 2021 05:20 AM
Solution
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.
Oct 06 2021 02:51 PM
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:
Best regards.
Oct 07 2021 11:43 AM
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.
Oct 21 2021 07:23 AM
Oct 05 2021 05:18 AM - edited Oct 05 2021 05:20 AM
Solution
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.