Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto update a cell with a current date | Excel Discussion (Misc queries) | |||
canceling auto date update | New Users to Excel | |||
How to turn off auto update on a formula | Excel Discussion (Misc queries) | |||
Auto-adjust cell height to accommodate contained text. | Excel Discussion (Misc queries) | |||
Can I disable auto formula update? | New Users to Excel |