ExcelBanter - Days formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Days formula (https://www.excelbanter.com/excel-discussion-misc-queries/158890-days-formula.html)

Hi there. I would like to create a formula that calculates the days that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when the
invoice is raised. Any ideas? Thanks

Joe,

With the date the invoice is raised in A1 put this in another cell

=EOMONTH(A1,0)+30 Format as date

This requires the analysis addin so Tools|addins and check it and it will
return a date 30 days from the end of the month the invoice was created.

Mike

"Joe" wrote:

Hi there. I would like to create a formula that calculates the days that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when the
invoice is raised. Any ideas? Thanks

On Wed, 19 Sep 2007 09:12:00 -0700, Joe wrote:

Hi there. I would like to create a formula that calculates the days that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when the
invoice is raised. Any ideas? Thanks

I'm not sure I understand your question.

The days that the invoice is outstanding is:

=TODAY() - Invoice_Date

If you don't want to start counting days until after the last day of the month
in which the invoice was generated, then:

=TODAY()-EOMONTH(Invoice_Date,0)

or, if you don't have or want the Analysis Tool Pak installed:

=TODAY()-DATE(YEAR(Invoice_Date),MONTH(Invoice_Date)+1,0)

If you want to return a zero if TODAY() is before the end of the month, then:

=MAX(0,TODAY()-DATE(YEAR(Invoice_Date),MONTH(Invoice_Date)+1,0))

or

=MAX(0,TODAY()-EOMONTH(Invoice_Date,0))

--ron

a. I have Date invoice in cell A1
b. I have a Payment due date in Cell A2. Before as =A1+$C$35 (Where I have
my credit terms eg 30 days)
c. I have =$I$25-B2 ($I$25 is where I have today date formula) so I get the
days the invoice is overdue

I have tried your formula but i get #Name? error

"Mike H" wrote:

Joe,

With the date the invoice is raised in A1 put this in another cell

=EOMONTH(A1,0)+30 Format as date

This requires the analysis addin so Tools|addins and check it and it will
return a date 30 days from the end of the month the invoice was created.

Mike

"Joe" wrote:

Hi there. I would like to create a formula that calculates the days that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when the
invoice is raised. Any ideas? Thanks

Did you read the bit of Mike's message where he said:
"This requires the analysis addin so Tools|addins and check it" ?
--
David Biddulph

"Joe" wrote in message
...
....
I have tried your formula but i get #Name? error

"Mike H" wrote:

Joe,

With the date the invoice is raised in A1 put this in another cell

=EOMONTH(A1,0)+30 Format as date

This requires the analysis addin so Tools|addins and check it and it will
return a date 30 days from the end of the month the invoice was created.

"Joe" wrote:

Hi there. I would like to create a formula that calculates the days
that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when
the
invoice is raised. Any ideas? Thanks

Which option do i need to install? Analysis ToolPak. I can see another 7
options?
Thanks,
jose

"David Biddulph" wrote:

Did you read the bit of Mike's message where he said:
"This requires the analysis addin so Tools|addins and check it" ?
--
David Biddulph

"Joe" wrote in message
...
....
I have tried your formula but i get #Name? error

"Mike H" wrote:

Joe,

With the date the invoice is raised in A1 put this in another cell

=EOMONTH(A1,0)+30 Format as date

This requires the analysis addin so Tools|addins and check it and it will
return a date 30 days from the end of the month the invoice was created.

"Joe" wrote:

Hi there. I would like to create a formula that calculates the days
that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when
the
invoice is raised. Any ideas? Thanks

Yes. Analysis ToolPak.

If you had looked up EOMONTH in Excel's help, it would have told you:
"If this function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in."
(and given you a "How?" link in case you didn't know how).

I don't want to give an "RTFM" answer to every post in this group, but a
large proportion of the answers can be found in Excel's help. It's always
worth trying there first, then asking here if you don't understand. There
is a lot of useful advice at http://www.cpearson.com/excel/newposte.htm
--
David Biddulph

"Joe" wrote in message
...
Which option do i need to install? Analysis ToolPak. I can see another 7
options?
Thanks,
jose

"David Biddulph" wrote:

Did you read the bit of Mike's message where he said:
"This requires the analysis addin so Tools|addins and check it" ?

"Joe" wrote in message
...
....
I have tried your formula but i get #Name? error

"Mike H" wrote:

Joe,

With the date the invoice is raised in A1 put this in another cell

=EOMONTH(A1,0)+30 Format as date

This requires the analysis addin so Tools|addins and check it and it
will
return a date 30 days from the end of the month the invoice was
created.

"Joe" wrote:

Hi there. I would like to create a formula that calculates the days
that the
invoice is outstanding. Now I have
=Today()
Date invoice plus 30 days
However, the 30 days should count after the end of the month of when
the
invoice is raised. Any ideas? Thanks