Simple Aging Column

[richene416]

I am trying to create an "aging" column that defines how old an invoice is
compared to today's date. I can get it for one cell, but cannot copy the
formula to the rest of the cells in the column.

I know there's got to be an easy fix for this, just haven't been able to
find anything after searching the web for over 2 hours.

Thanks for your help!
Donna

 

[Ron Coderre]

If you post your formula, even though it doesn't work, we'll have a much
better understanding of your situation.

Regards,

Ron
Microsoft MVP - Excel

 

[Dave]

Hi Donna,
Can you post the formula that's working for the one cell?
Regards - Dave

 

[richene416]

Sorry about that - didn't even think about it!

I'm using =DATEDIF(A1,C2,"d")

I have today's date in A1, then have a column with date of invoice and I'm
wanting to create a column that shows the aging of the invoice from today's
date.

 

[richene416]

Sorry about that - didn't even think about it!

I'm using =DATEDIF(A1,C2,"d")

I have today's date in A1, then have a column with date of invoice and I'm
wanting to create a column that shows the aging of the invoice from today's
date.

 

[Rick Rothstein \(MVP - VB\)]

The problem I think you are having is when you copy your formula down, the
A1 reference is incrementing just like the C2 reference is, but only A1
contains today's date. That means you need to specify A1 using absolute
references so it doesn't increment. This will probably work for you...

=DATEDIF($A$1,C2,"d")

Two things, though. One, I would point out that you don't need to reference
A1 at all... you could use the built-in TODAY() function instead...

=DATEDIF(TODAY(),C2,"d")

However, two, you don't need to use DATEDIF to calculate the number of
days... just subtract the smaller date value from the larger one, like
this...

=TODAY()-C2

You can do this because Excel's dates are really just integer offsets form
some "date zero" in the past simply formatted to look like a date you would
recognize as a date. You can see the underlying integer offset values quite
easily... just multiply a date by 1. For example, put =TODAY() in one cell
and =1*TODAY() in another... to Excel, they are both the same value.

Rick

 

[Ron Coderre]

Thanks for posting the formula.

If Col_C contains dates, with no time component,
you could probably just use this to calculate the elapsed days:
=$A$1-C2

If you need to stratify the elapsed days into categories,
like 0-30, 31-60, 61-90, Over 90...Try something like this:

H1:I4 contains this list:
0 0-30
31 31-60
61 61-90
91 Over 90

This formula associates the age in days with an age category:
D2: =VLOOKUP($A$1-C2,$H$1:$I$4,2,1)

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

 

[richene416]

Thank you for your help! It worked!!!

The problem I think you are having is when you copy your formula down, the
A1 reference is incrementing just like the C2 reference is, but only A1
contains today's date. That means you need to specify A1 using absolute
references so it doesn't increment. This will probably work for you...

=DATEDIF($A$1,C2,"d")

Two things, though. One, I would point out that you don't need to reference
A1 at all... you could use the built-in TODAY() function instead...

=DATEDIF(TODAY(),C2,"d")

However, two, you don't need to use DATEDIF to calculate the number of
days... just subtract the smaller date value from the larger one, like
this...

=TODAY()-C2

You can do this because Excel's dates are really just integer offsets form
some "date zero" in the past simply formatted to look like a date you would
recognize as a date. You can see the underlying integer offset values quite
easily... just multiply a date by 1. For example, put =TODAY() in one cell
and =1*TODAY() in another... to Excel, they are both the same value.

Rick

 

[richene416]

Thank you so much - between you all I got it to work!

 

[Ron Coderre]

You're welcome......I'm glad I could help.

Regards,

Ron
Microsoft MVP - Excel

 

[Gord Dibben]

Early date first......later date second.

=DATEDIF(C2,$A$1,"d")

The $ signs fix A1 as Absolute date.

Or use the TODAY() function and no date in A1

=DATEDIF(C2,TODAY(),"d")


Gord Dibben MS Excel MVP