• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Put value in correct Month Col based on date of invoice payment terms [SOLVED]

C

Cammandk

Member
Hi, I'm working on a cashflow sheet and I need to have the value of an invoice put into the correct month column based upon the date of the invoice and the payment terms.

[pre]
Code: 
A	B	         C	D	E	F         G
1	Date	        Value	Terms	Jan	Feb	Mar
2	01-Jan-13 	100	0	100
3	10-Jan-13       200	30		200
4	20-Jan-13       300	60			300
[/pre]
Also as a final need if Terms ="P" then the value needs to go in the current month.


I need this to work per single line as my rows are not necessarily sequential


Hope you can help.

thanks
 
Hi ,


Can you check this file ?


https://www.dropbox.com/s/nyxz5klw5oer2se/Cash_flow_Assignment.xlsx


I have used dates such as 1/1/2013 ( for Jan ) , 2/1/2013 ( for Feb ) and so on , in the header row , since this makes the formula simpler.


Narayan
 
Thank you for this it works very well and I can almost understand it.

There is one thing that it is not quite doing as I need - may have been my explaination.


If the Term is "P" then I need the value to be put in the current month - and not based upon the date of the invoice. The "P" represents paid - this is a one off posting. I don't want this value to move as the months then proceed.


Thanks

David
 
Hi David ,


Can you replace the existing formula in D2 with this one ?


=IF(TEXT(EDATE(IF($C2="P",TODAY(),$A2),IF($C2="P",0,$C2)/30),"yyyymm")=TEXT(D$1,"yyyymm"),$B2,"")


Copy this across and down.


Narayan
 
Sorry one last thing. Copied rows down but because no values in yet - getting #value error in cells - can this be cleared
 
Hi David ,


I don't get a #VALUE error , even when I copy the formula down.


Can you copy + paste the formula you have in any cell , say D2 ?


Narayan
 
Hi Narayan

Can you assist me with this. I'm using the formula below and I need to update it. Tried but to no avail.
I have a field X18 that may have a date in. If it does this is the date that should be used not that H18 and the amount in S18 should be put same period at the date in X18. Hope that makes sense.
Thanks
David

=IF($G18="R",0,IF(TEXT(EDATE(IF($AO18="P",TODAY(),$H18),IF($AO18="P",0,$AO18)/30),"yyyymm")=TEXT(AS$5,"yyyymm"),$S18,""))
 
Hi David ,

I am not able to correlate between the formula you have posted and the explanation of the change that you want to make.

The formula you have posted refers to the following cells :

G18 , AO18 , H18 , AS5 , S18

For the change that you want , how many of the above references , and which of them , will remain the same ? Which of them will change , and how ?

Narayan
 
Hi Narayan

Sorry for the delay in replying to this but the system would not let me post.
Anyway.

G18 = a Text field. If it has "R" the formulas basically ignores it.
H18 = date field of the invoice
AO18 = payment terms of invoice - 0,30,60 days etc
S18 = amount of invoice
AS5 = Month/year of column

So the formula takes the date and adds on the payment term days and then puts the value of the invoice into the correct month column. The except to this is that if AO = "Paid" then it uses Today() as the date not G18.

I want to keep all this but I want the option that if a date is in X18 then the formula uses the date in X18 instad of H18 and then puts the value S18 into the correct Month Column. Payment terms would effectively be 0 in this instance.

David
 
You must log in or register to reply here.
Back
Top