I have a spreadsheet with rows showing payments and the day in the month that they are due. There are also columns showing the weeks of the year, identified by their start dates. I would like to add the payment due for that row in any week where the date due falls between the start date and end date for that week.
The file attached has been populated manually to show what I'm trying to achieve. Can anyone help
a spreadsheet with rows showing payments and the day in the month that they are due. There are also columns showing the weeks of the year, identified by their start dates. I would like to add the payment due for that row in any week where the date due falls between the start date and end date for that week.
The file attached has been populated manually to show what I'm trying to achieve. Can anyone help, please?
There is no attachment. Please try again and don't forget to press "Start upload" before you submit your reply.
The file is now shown attached to this reply (although I thought that I did the same process in the original post!)
I thinks that wouldn.t be a problem, what I do jot understand is why the Last payment days are not in ascending order as well as the columns G-K
And least of all, quite important due to te many new functions available in newer excel version, which version are you using?
I agree with Hans that the dates in columns G:K don't seem to make much sense. I guess the are merely examples to show that a payments should show in the weeks starting on those dates.
The attached workbook contains a solution that may be what you are after, based on Power Query. For each payments it determines first day of the week for each of the coming 12 payment dates. The 12 months is hard-coded in this example but can easily be made dynamic. Finally, the amounts are summarized per week.
Finding a formula based solution would be more complicated. At least that's what I believe. Let me know if this is something you could work with.
I used your initial formula in G2 and built on it.
This this and see if it gives you the expected result.
The explanation is in a text box on the same sheet
You can always add a sum under each column G-K to see what the totals are for those dates.
Hello,
Attached is a formula version based on what you have entered manually, I might have got the wrong understanding, but it is adjustable so just tweak it till it fits your need. As it is the week number for when the day of month occurs that seems crucial I am checking for the week number, if true then show the value.
Br,
Anders
Thanks Anders; that works beautifully. The only problem is that I can't see your formula; the only thing visible in the calculated cells is "=IF("
Is there a way to make the rest of the formula visible so that I can try it on my live data?
Stuart
Thanks Hans. I'm using 365 version 2305.
The list of suppliers already exists in order of our original arrangements with them and the date of payment is therefore unordered.
Thanks Riny. The dates are the Friday of each week in the period under review (actually the live data shows all 52 weeks in our financial year). Your file works well but I can't see how!
Stuart
I tried some things and changed two things, I used Ander's file as a starting point (Try-Outs sheet with many extra formulas.
Your original table converted to an Excel ListObject Table and added formulas toi calculate the week number and the start date of that week
Another sheet with a pivot table and a slicer to filter it
The days due and date due are things you will have to explain better since all are i the past.
A added a duplicate Supplier 01 so it shows up in the Pivot table
The Suppliers are all 01 to 13 so that when you sort them, they remain in the supplier's name in ascending order
In response to post nr 10:
I used Power Query. If you are new to that, look at the formula based solutions first.
Firstly I'd like to thank all the responders for their help. Secondly I need to apologise for the confusion I caused with the odd dates in the example spreadsheet; you will see that I put in a February date to see how any solution would deal with short months.
I am surprised that there isn't an established solution to this already as it seems to me to be something t hat would come up frequently in business.
Finally, I came up with my own solution. It is quite crude but it has the benefit that I understand it and can therefore explain it to my boss, so that he will trust it going forward. Basically I test to see if the string created from the due date is found within a string of the dates within the week in question, entering the amount payable if true, or an empty string otherwise.
=IF(ISNUMBER(SEARCH ("/"&$E11&"/","/"& DAY(J$3+1)&"/"& DAY(J$3+2)&"/"& DAY(J$3+3)&"/"& DAY(J$3+4)&"/"& DAY(J$3+5)&"/"& DAY(J$3+6)&"/")),$F11),"")
I realise that this is probably very inefficient compared to your more sophisticated solutions but any extra processing time is unnoticeable with the modest size of our cashflow model.
Stuart
Hello Stuart,
If you expand the formula bar you will see the rest of the formula, you can either click on the arrow on far right, press CTRL + SHIFT + U or adjust the field size with the mouse pointer by hovering over the lower edge of the formula bar, when you see a white double pointed arrow, click and drag.
The formula in cell G2 is:
=IF(
(ISOWEEKNUM(G$1)=ISOWEEKNUM(DATE(YEAR(G$1),MONTH(G$1),$C2)))+
(ISOWEEKNUM(G$1)=ISOWEEKNUM(DATE(YEAR(G$1),MONTH(G$1)+1,$C2))),
$E2,"")
Br,
Anders
Thanks Anders; that's a new one on me!
Stuart
