Home 
Search 
Today's Posts 
#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 
#4




IFS for multiple Arguments
Hello!
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! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Find days in a Month  Excel Discussion (Misc queries)  
Find the First of the Month after 60 days  Excel Worksheet Functions  
find the beginning and end days of a project  New Users to Excel  
how would i use excel to find the number of days  Excel Discussion (Misc queries)  
how to find nos. of each day in certain nos. of days?  Excel Discussion (Misc queries) 