Simple Aging Column
 

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

Simple Aging Column
 

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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

Simple Aging Column
 

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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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.

"Dave" wrote:

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

Simple Aging Column
 

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" wrote in message
...
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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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" wrote in message
...
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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

Thank you for your help! It worked!!!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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

"Ron Coderre" wrote:

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" wrote in message
...
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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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

Regards,

Ron
Microsoft MVP - Excel

"richene416" wrote in message
...
Thank you so much - between you all I got it to work!

"Ron Coderre" wrote:

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" wrote in message
...
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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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

Simple Aging Column
 

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


On Tue, 3 Jun 2008 08:55:00 -0700, richene416
wrote:

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.

"Ron Coderre" wrote:

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

"richene416" wrote in message
...
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