+ Reply to Thread
Results 1 to 37 of 37

Formulas to arrange payment per dates

  1. #1
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Formulas to arrange payment per dates

    Hi,

    I am working on one excel work sheet were i need please help on..i have created dummy template attached here.

    It has four sheets of calculation..

    1. Delivery
    2, cost of delivery
    3. Payment date
    4. Payment arrange per week

    Up to sheet 3 i am fine, but i am struggling to make sheet 4, here i want to arrange payment from sheet 3 to particular week but unable to figure out any formula or any method to do that.

    can anyone please help..?

    thanks

    Nadim
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Formulas to arrange payment per dates

    crossposted: https://www.excelguru.ca/forums/show...ment-per-dates

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formulas to arrange payment per dates

    In C5 copied down and across
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Sorry alansidman I was unaware about crossposted.. but thanks for flagging up..

  5. #5
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    ChemistB thank you so much for helping me out, it looks promising, i will try it on actual spreadsheet and if any issue i will get back to you..

  6. #6
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Hi ChemistB,

    I have tried putting same formula another spreadsheet but value comes out to "0" all the time, i also tried on same copy of the file in which you entered the formula, but there also it shows zero only. i have attached the copy here for you to look at. also for curiosity on the formula it says "PayData" but there is no pay data on the file..? i am not sure if i understand it correctly..

    Thanks

    Nadim
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formulas to arrange payment per dates

    Sorry, I forgot to mention that I created a named range (Name Manager on Formula tab) PayData = Payment_Date!$E$5:$O$7.
    Alternately, you can replace PayData with Payment_Date!$E$5:$O$7

  8. #8
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Thank you so much ChemistB, this is all working now!!

  9. #9
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Hi ChemistB,

    Sorry to keep bothering you again, it is still not putting value to right column (date), i noticed that it is putting value to the closest date between two w/c dates.

    i.e on attached spread sheet, on sheet ( Payment date) - supplier 1 date is 30/12/2020 and it has value( £1), this date falls between w/c 25/12/2020 and w/c 01/01/2021, so it should put value (£1) on to sheet (Pay per week) on w/c 25/12/2020 as the date 30/12/2020 is after 25/12/2020 and before 01/01/2021 , but problem here is it putting this value on w/c 01/01/2021 considering it is closest to that date.

    I have other examples also highlighted in the sheet.

    would be really helpful if you please review formula and guide back to me.?

    Thanks

    Nadim
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: Formulas to arrange payment per dates

    Try the following array entered formula** in cell C5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Be sure to activate the formula as described above before copying over and down.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Jete,

    Thanks you so much for your input, your solution is perfect!, however I noticed, this has slow down the main spreadsheet were I am working on, I have copied formulas across 200 rows and DF column, is there any thing I can to stop it from slowing spreadsheet..? or is it that array formulas always work like this slow..?

    regards

    Nadim

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formulas to arrange payment per dates

    In what way did you change the formula? Are you now using full column references? 200 rows is not very much ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    AliG,

    Below is the formula i am using,the only

    =SUM(IFERROR((Payment_Date!$A$5:$A$150=$A5)*(Payment_Date!$F$5:$DF$150)*(Payment_Date!$E$5:$DE$150>=C$4)*(Payment_Date!$E$5:$DE$150<C$4+7),0)) - Using

    =SUM(IFERROR((Payment_Date!$A$5:$A$7=$A5)*(Payment_Date!$B$5:$B$7=$B5)*(Payment_Date!$E$5:$O$7)*(Payment_Date!$D$5:$N$7>=C$4)*(Payment_Date!$D$5:$N$7<C$4+7),0)) - Recomended

    the only section i took out is Payment Date!$B$5:$B$7=$B5 which i thought it was unnecessary. other wise i just has to change column names i.e E to F


    thanks

    Nadim

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formulas to arrange payment per dates

    I can't read the second formula - please change the colour chosen.

    Why would you assume that something is unnecessary?

  15. #15
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    AliG,

    I would take it out because i know it is not relevant, it refer to supplier list, and supplier list is not necessary for this calculation as we are calculating based on matching items.

    Below is the link again highlighted in red either i put on or not put on it does not affected either way.

    I have copied same formula on to attached sheet which we were using for demo. you can add any qty on cost tab and once we refresh it take a while update remaining.

    struggling to understand what is making it slowing down

    your help would be appreciated.


    =SUM(IFERROR((Payment_Date!$A$5:$A$7=$A5)*(Payment_Date!$B$5:$B$7=$B5)*(Payment_Date!$E$5:$O$7)*(Payment_Date!$D$5:$N$7>=C$4)*(Payment_Date!$D$5:$N$7<C$4+7),0))

    Thanks

    Nadim
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    =SUM(IFERROR((Payment_Date!$A$5:$A$7=$A5)*(Payment_Date!$B$5:$B$7=$B5)*(Payment_Date!$E$5:$O$7)*(Payment_Date!$D$5:$N$7>=C$4)*(Payment_Date!$D$5:$N$7<C$4+7),0))

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    I added a few rows to "Cost" and changed values in Column D and there no obvious performance issues.

    What do you mean by "when we refresh" ?

  18. #18
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Hi John,

    That's strange,

    Have you tried putting value in cost - column -H5 to H150, i.e. as it is in column F, and Pay per week sheet updated without delay..?

    when i am doing above my sheet Pay per week don't update hence I then click on refresh and then it shows on bottom (calculating (4 threads) : %)

    Nadim

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formulas to arrange payment per dates

    Do you have the workbook set to manual calculations?

  20. #20
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    AliG,

    it is on automatic, does it work fine on your PC..?

    I have even tried on different PC but it is still slow..

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formulas to arrange payment per dates

    I don't have your file on my machine.

  22. #22
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    It is attached on my post reply NO. #15

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formulas to arrange payment per dates

    I was not offering to check it - sorry. I don't understand well enough what John has done to help you, but if it's working for John, then the issue is at your end.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    Following your instruction I put Qty in column H and YES it was recalculating slowly. But you have 50 columns x 150 rows of Array calculations in "Pay_per_week" so perhaps it is not too surprisiing.
    Last edited by JohnTopley; 07-05-2021 at 10:57 AM.

  25. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: Formulas to arrange payment per dates

    See if the following for cell C5, down and across, is faster:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    On my m/c it is slower [at almost 100% CPU usage!]

  27. #27
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    JeteMC,

    Sorry but this is even more slower then previously, literally it crash..

    Nadim

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: Formulas to arrange payment per dates

    Then my suggestion would be to unpivot the data on the Payment_Date sheet, add another column and get the results using a pivot table as modeled in the attached file.
    Unfortunately, in this case, I don't believe that the "un-pivoting" can be accomplished using Power Query.
    The formula for the new column (Payment WC) is: =IF([@Qty]=0,"",[@[Payment date]]-WEEKDAY([@[Payment date]])-1)
    Let us know if you have any questions.

  29. #29
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    I believe this is over complicated and the results could be derived by a simple MATCH on WC dates vs the Payment Dates to determine in which week payment is due.

    "Payment Data" and "Pay_per_week" are effectively duplicates as "Payment Dates" can be derived from WC dates and Payment terms.

    And "Cost" also has much duplication.

    Or have I completely misunderstood (not for the first time!)

    Update:

    I tried SUMPRODUCT but this also was slow: I suspect only a redesign will overcome the performance problems as there is a large amount of array processing.

    You can improve performance by limiting the "calculation period" which is currently a year: can this be limited to (say) 4 weeks?
    Last edited by JohnTopley; 07-05-2021 at 03:46 PM.

  30. #30
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    Looking further: does the "Cost/Kg" not vary over a period (no increase/decrease within a year) ?

    Is tab "Delivery Date" replaced by tab "Cost" ?

  31. #31
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,426

    Re: Formulas to arrange payment per dates

    Going back and again responding to post #27.
    I applied the AGGREGATE formula from post #25 to the Pay_per_week sheet cells C5:BC150 of the file attached to post #15 and the results took about 3 seconds.
    I want to make sure that the formula is being activated by simply pressing the Enter key.

  32. #32
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    @JeteMc,
    Repeating your test (post #31) took between 4-8 seconds after adding an entry and pressing the Enter key.

  33. #33
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Yes this was entered through enter key only, but still same slow.. I don't understand why is it so slow on my machine even if it is not array formula, previously with same amount of formula i used from ChemistB on post #3 and it was not slowing down, but it has another problem of adding value to closest w/c.

    with regards to post #28 think it will create lot more complex when it will come to amending supplier or items, i have to manage it 52 time manually.. as in current format i just have to add once and can drag down/across formulas. - i am sure there may be a simplest way to do that but i currently dont know how to.

  34. #34
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    it has another problem of adding value to closest w/c.
    Is this a problem ? and what is the issue?

  35. #35
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Quote Originally Posted by JohnTopley View Post
    Is this a problem ? and what is the issue?
    John it is explained on post #9

  36. #36
    Registered User
    Join Date
    05-25-2021
    Location
    Leicester
    MS-Off Ver
    2019 Professional Plus
    Posts
    35

    Re: Formulas to arrange payment per dates

    Is there any way i can put only one worksheet - pay_per_week on manual calculation instead of whole workbook..? or can we do anything with VBA that when i click on button then it put array formula in all the cell on Pay_per_week sheet and calculate value, and once i click on another button it makes all value to pastespecial value..? in this way i can update all the details on all other sheets and once ready then go to Pay_per_week and update everything whenever needed.

  37. #37
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formulas to arrange payment per dates

    So

    =MATCH(Data,Range(Dates_in_ascendingOrder),1) will give the correct result e.g C4:WC4 in "Pay_per_week"
    _
    30/12/2020 will fall in WC 25/12/2020

    However I am confused as to what formulas are required where!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Payment Schedule Formulas Excel
    By lanespoli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2019, 11:03 PM
  2. Payment Schedule Formulas Excel
    By lanespoli in forum Access Tables & Databases
    Replies: 1
    Last Post: 09-18-2019, 11:50 PM
  3. Payment dates VBA
    By KarelMusa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2019, 09:26 AM
  4. Distribution payment by sales formulas
    By payeekvadder in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-06-2018, 01:39 AM
  5. Replies: 1
    Last Post: 02-04-2016, 01:03 PM
  6. Formulas to auto arrange in ascending
    By cyee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 07:45 AM
  7. Billing and Payment Dates
    By CubedSphere in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2012, 07:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1