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

With the invoice date in E34, try:

=E12+32-DAY(E12+32)+30

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Joe" <(E-Mail Removed)> wrote in message
news:B71AEEF6-5CF8-47BD-BCA9-(E-Mail Removed)...
> 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 <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:7F5592FD-C517-4624-9982-(E-Mail Removed)...
....
> 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" <(E-Mail Removed)> wrote in message
> news:7F5592FD-C517-4624-9982-(E-Mail Removed)...
> ....
> > 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" <(E-Mail Removed)> wrote in message
news:3CF69177-ABFC-4989-A563-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:7F5592FD-C517-4624-9982-(E-Mail Removed)...
>> ....
>> > 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
>>
>>
>>