How to auto update date contained in formula
 

I need to count the number of cells with dates that have been reached (as of
today) and count the number of cells that hold future dates. For example,
Column "A" holds all past, present (today) and future dates. My formula
states =COUNTIF(A2:A17,"<=today's date") to count the number of cells with
dates that have been reached as of today, and =COUNTIF(A:A17,"today's date")
to count the number of cells that hold future dates.
That's the part where I am stuck. I don't know how to write the formula so
Excel can automatically update every day to the current date. Right now, I
am resorting to manually writing today's date into the formula or by entering
it as Ctrl +;. I have tried to write today's date as TODAY(), hoping that
Excel would automatically bring up the date in the formula. Instead, I am
getting a zero return on the cell count. I'm sure that this is a no-brainer
for many, but a brain-wrecker for me and I'd appreciate anyone's help!
Thanks in advance.
wmc

How to auto update date contained in formula
 

=COUNTIF(A2:A17,"<="&today())

or you could put the date in a cell:
=COUNTIF(A2:A17,"<="&a99)

or you could put the date in the formula:
=COUNTIF(A2:A17,"<="&date(2007,4,17))




wmc wrote:

I need to count the number of cells with dates that have been reached (as of
today) and count the number of cells that hold future dates. For example,
Column "A" holds all past, present (today) and future dates. My formula
states =COUNTIF(A2:A17,"<=today's date") to count the number of cells with
dates that have been reached as of today, and =COUNTIF(A:A17,"today's date")
to count the number of cells that hold future dates.
That's the part where I am stuck. I don't know how to write the formula so
Excel can automatically update every day to the current date. Right now, I
am resorting to manually writing today's date into the formula or by entering
it as Ctrl +;. I have tried to write today's date as TODAY(), hoping that
Excel would automatically bring up the date in the formula. Instead, I am
getting a zero return on the cell count. I'm sure that this is a no-brainer
for many, but a brain-wrecker for me and I'd appreciate anyone's help!
Thanks in advance.
wmc

--

Dave Peterson

How to auto update date contained in formula
 

Try this:

=SUMPRODUCT(--(A2:A17<=TODAY()))

HTH,

Barb Reinhardt
"wmc" wrote:

I need to count the number of cells with dates that have been reached (as of
today) and count the number of cells that hold future dates. For example,
Column "A" holds all past, present (today) and future dates. My formula
states =COUNTIF(A2:A17,"<=today's date") to count the number of cells with
dates that have been reached as of today, and =COUNTIF(A:A17,"today's date")
to count the number of cells that hold future dates.
That's the part where I am stuck. I don't know how to write the formula so
Excel can automatically update every day to the current date. Right now, I
am resorting to manually writing today's date into the formula or by entering
it as Ctrl +;. I have tried to write today's date as TODAY(), hoping that
Excel would automatically bring up the date in the formula. Instead, I am
getting a zero return on the cell count. I'm sure that this is a no-brainer
for many, but a brain-wrecker for me and I'd appreciate anyone's help!
Thanks in advance.
wmc

How to auto update date contained in formula
 

Dave,
I picked your first suggestion and it's working like a charm.
Thanks for the education!
--
wmc


"Dave Peterson" wrote:

=COUNTIF(A2:A17,"<="&today())

or you could put the date in a cell:
=COUNTIF(A2:A17,"<="&a99)

or you could put the date in the formula:
=COUNTIF(A2:A17,"<="&date(2007,4,17))




wmc wrote:

I need to count the number of cells with dates that have been reached (as of
today) and count the number of cells that hold future dates. For example,
Column "A" holds all past, present (today) and future dates. My formula
states =COUNTIF(A2:A17,"<=today's date") to count the number of cells with
dates that have been reached as of today, and =COUNTIF(A:A17,"today's date")
to count the number of cells that hold future dates.
That's the part where I am stuck. I don't know how to write the formula so
Excel can automatically update every day to the current date. Right now, I
am resorting to manually writing today's date into the formula or by entering
it as Ctrl +;. I have tried to write today's date as TODAY(), hoping that
Excel would automatically bring up the date in the formula. Instead, I am
getting a zero return on the cell count. I'm sure that this is a no-brainer
for many, but a brain-wrecker for me and I'd appreciate anyone's help!
Thanks in advance.
wmc

--

Dave Peterson

How to auto update date contained in formula
 

Barb,
It's great to know that there is an alternative to =COUNTIF... and I thank
you for the quick crash course in "Excel for Newbies"!
--
wmc


"Barb Reinhardt" wrote:

Try this:

=SUMPRODUCT(--(A2:A17<=TODAY()))

HTH,

Barb Reinhardt
"wmc" wrote:

I need to count the number of cells with dates that have been reached (as of
today) and count the number of cells that hold future dates. For example,
Column "A" holds all past, present (today) and future dates. My formula
states =COUNTIF(A2:A17,"<=today's date") to count the number of cells with
dates that have been reached as of today, and =COUNTIF(A:A17,"today's date")
to count the number of cells that hold future dates.
That's the part where I am stuck. I don't know how to write the formula so
Excel can automatically update every day to the current date. Right now, I
am resorting to manually writing today's date into the formula or by entering
it as Ctrl +;. I have tried to write today's date as TODAY(), hoping that
Excel would automatically bring up the date in the formula. Instead, I am
getting a zero return on the cell count. I'm sure that this is a no-brainer
for many, but a brain-wrecker for me and I'd appreciate anyone's help!
Thanks in advance.
wmc