Hi
I need help with a formula that will help to put the values in the right payment due coloumn based on the transaction month and payment term.
Below is an example and where I want to pull the amount payable to the over due coloumn, or Dec 22 or Jan 23 coloumn based on the transaction month and payment
Appreciate any help either a Formula or a simple Macro
Please see attached Excel
Last edited by samyraj; 12-12-2022 at 04:02 AM.
Hello samyraj.
In the upload the specific criteria which determines which column the output goes is not intuitive to me. For instance you have two Transactions in the month of November with output in two different due columns.
Please explain further.
Dave
Hi Dave
One supplier invoice is with a November date is with a payment terms 30 days End of month therefore the invoice is payable in Dec 22. The other invoice with a November date is with a payment term 60 days end of month therefore this invoice is payable in Jan 23 therefore the amount is sitting in the Jan 23 column
in h2 copy down and accross
=IF(EDATE($C2,SUBSTITUTE($F2," EOM","")/30)=H$1,$E2,"")
in g2
=IF(EDATE($C2,SUBSTITUTE($F2," EOM","")/30)<DATE(YEAR(TODAY()),MONTH(TODAY()),1),$E2,"")
Another way:
Please try in H2 and copy down and across:Formula:
Please Login or Register to view this content.
In G2
=IF($B2+$F2<H$1,$E2,"")
in H2
=IF(AND($B2+$F2>=H$1,$B2+$F2<=EOMONTH(H$1,0)),$E2,"")
Copy across to I
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I forgot the Over Due column: Please try in G2:Formula:Please Login or Register to view this content.
Thank you everyone much appreciated . I found davsth's formula very useful
You are welcome, thanks for the feedback.
Administrative Note:
Please note for future threads!
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly.
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.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks