Days formula

[Guest]

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

 

[Guest]

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

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[David Biddulph]

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

 

[Guest]

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

 

[David Biddulph]

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