- **New Users to Excel**
(*https://www.excelbanter.com/new-users-excel/*)

- - **To find due days of debtors**
(*https://www.excelbanter.com/new-users-excel/262188-find-due-days-debtors.html*)

To find due days of debtorsHi 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 |

To find due days of debtorsWhat 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 |

To find due days of debtorsBrad,
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 |

IFS for multiple ArgumentsHello!
To properly categorize this, you can use the multiple IF function in excel, which is the IFS Function. IFS Function works like this: =ifs(argument 1, value if true 1, argument 2, value if true 2, ....) so you can use this for multiple arguments. Regardless using days/months, this formula is ideal in categorizing your invoices. Also you can use =Today() so that Excel can automatically update what items are due and how many days are there remaining before an invoice is due. Hope this helps! |

All times are GMT +1. The time now is 01:40 AM. |

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.

ExcelBanter.com