If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
To find due days of debtors
Hi all,
I am using office2007. I have an excel sheet with the following colimn invoicedate amount period. How I can give a formula i n period column as if current month - month(invoicedate) = 0 then period='Current' if current month - month(invoicedate) = 1 then period='30' if current month - month(invoicedate) = 2 then period='60' if current month - month(invoicedate) = 3 then period='90' else period='120' it should taken care 30 or 31 or 28 and 29 days for correspodning month. Please help have any idea With thanks Pol |
#2
|
|||
|
|||
To find due days of debtors
What happens when you do this on the first day of the month and the invoice
is for the last day of the previous month - do you want this to be zero or one Assuming that you want this to be zero =MIN(4,DATEDIF(B17,TODAY(),"M"))*30 =where the invoice date is in cell B17 if you want this to be one - you would also need to compare years Do you need this formula? -- Wag more, bark less "pol" wrote: Hi all, I am using office2007. I have an excel sheet with the following colimn invoicedate amount period. How I can give a formula i n period column as if current month - month(invoicedate) = 0 then period='Current' if current month - month(invoicedate) = 1 then period='30' if current month - month(invoicedate) = 2 then period='60' if current month - month(invoicedate) = 3 then period='90' else period='120' it should taken care 30 or 31 or 28 and 29 days for correspodning month. Please help have any idea With thanks Pol |
#3
|
|||
|
|||
To find due days of debtors
Brad,
I was about to ask much the same question regarding last/first of the month. And without considering years, then there are real problems when invoice date is in the previous year (as you are obviously aware). pol- can't you just consider number of days rather than months? By using just number of days, then you can easily determine if it is 30, 60, 90 or 120 or more days past due. "Brad" wrote: What happens when you do this on the first day of the month and the invoice is for the last day of the previous month - do you want this to be zero or one Assuming that you want this to be zero =MIN(4,DATEDIF(B17,TODAY(),"M"))*30 =where the invoice date is in cell B17 if you want this to be one - you would also need to compare years Do you need this formula? -- Wag more, bark less "pol" wrote: Hi all, I am using office2007. I have an excel sheet with the following colimn invoicedate amount period. How I can give a formula i n period column as if current month - month(invoicedate) = 0 then period='Current' if current month - month(invoicedate) = 1 then period='30' if current month - month(invoicedate) = 2 then period='60' if current month - month(invoicedate) = 3 then period='90' else period='120' it should taken care 30 or 31 or 28 and 29 days for correspodning month. Please help have any idea With thanks Pol |
Thread Tools | |
Display Modes | |
|
|