Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 22nd 10, 02:45 PM posted to microsoft.public.excel.newusers
pol pol is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 129
Default 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   Report Post  
Old April 22nd 10, 03:16 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 846
Default 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   Report Post  
Old April 22nd 10, 03:21 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,203
Default 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   Report Post  
Old July 2nd 21, 08:13 AM
Member
 
First recorded activity by ExcelBanter: Jun 2021
Posts: 47
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find days in a Month Jman Excel Discussion (Misc queries) 3 October 13th 08 07:00 AM
Find the First of the Month after 60 days NeSchw6G Excel Worksheet Functions 11 August 23rd 08 05:46 PM
find the beginning and end days of a project liu New Users to Excel 5 April 9th 08 12:44 AM
how would i use excel to find the number of days------- joe hunter[_2_] Excel Discussion (Misc queries) 2 June 7th 07 02:43 AM
how to find nos. of each day in certain nos. of days? Khan Excel Discussion (Misc queries) 2 September 17th 06 07:57 PM


All times are GMT +1. The time now is 03:49 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017