| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I check whether a column of dates are all before today's d
I'm working in Excel 2007.
I've got a column of dates, and I need to check the column as a whole to ensure that every date is prior to today's date. E.g. if today's date is 10/19/2007, and my column is the following: 8/17/1940 4/15/2007 10/18/2007 .... the data is okay; 0 of the dates violate the rule. However, if the column is the following: 8/17/1940 4/15/2007 10/20/2007 .... then it's not okay, because a date (the third) violates the rule - it falls after today's date. I thought I could do it using COUNTIF(A:A,"TODAY()"), where A is the column of dates, but that function always returns a 0 value, no matter whether the dates are okay or not. If I replace TODAY() with a date, it works - for some reason, it just won't recognize and evaluate the TODAY() function. What am I doing wrong? I appreciate the help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I check whether a column of dates are all before today's d
Try this:
=COUNTIF(A:A,"<"&TODAY())=COUNT(A:A) The result will be either TRUE or FALSE. -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I'm working in Excel 2007. I've got a column of dates, and I need to check the column as a whole to ensure that every date is prior to today's date. E.g. if today's date is 10/19/2007, and my column is the following: 8/17/1940 4/15/2007 10/18/2007 ... the data is okay; 0 of the dates violate the rule. However, if the column is the following: 8/17/1940 4/15/2007 10/20/2007 ... then it's not okay, because a date (the third) violates the rule - it falls after today's date. I thought I could do it using COUNTIF(A:A,"TODAY()"), where A is the column of dates, but that function always returns a 0 value, no matter whether the dates are okay or not. If I replace TODAY() with a date, it works - for some reason, it just won't recognize and evaluate the TODAY() function. What am I doing wrong? I appreciate the help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I check whether a column of dates are all before today
Works great! (Now I've just gotta figure out WHY!)
Yay, an opportunity to learn something new. Thanks again! "T. Valko" wrote: Try this: =COUNTIF(A:A,"<"&TODAY())=COUNT(A:A) The result will be either TRUE or FALSE. -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I'm working in Excel 2007. I've got a column of dates, and I need to check the column as a whole to ensure that every date is prior to today's date. E.g. if today's date is 10/19/2007, and my column is the following: 8/17/1940 4/15/2007 10/18/2007 ... the data is okay; 0 of the dates violate the rule. However, if the column is the following: 8/17/1940 4/15/2007 10/20/2007 ... then it's not okay, because a date (the third) violates the rule - it falls after today's date. I thought I could do it using COUNTIF(A:A,"TODAY()"), where A is the column of dates, but that function always returns a 0 value, no matter whether the dates are okay or not. If I replace TODAY() with a date, it works - for some reason, it just won't recognize and evaluate the TODAY() function. What am I doing wrong? I appreciate the help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I check whether a column of dates are all before today
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... Works great! (Now I've just gotta figure out WHY!) Yay, an opportunity to learn something new. Thanks again! "T. Valko" wrote: Try this: =COUNTIF(A:A,"<"&TODAY())=COUNT(A:A) The result will be either TRUE or FALSE. -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... I'm working in Excel 2007. I've got a column of dates, and I need to check the column as a whole to ensure that every date is prior to today's date. E.g. if today's date is 10/19/2007, and my column is the following: 8/17/1940 4/15/2007 10/18/2007 ... the data is okay; 0 of the dates violate the rule. However, if the column is the following: 8/17/1940 4/15/2007 10/20/2007 ... then it's not okay, because a date (the third) violates the rule - it falls after today's date. I thought I could do it using COUNTIF(A:A,"TODAY()"), where A is the column of dates, but that function always returns a 0 value, no matter whether the dates are okay or not. If I replace TODAY() with a date, it works - for some reason, it just won't recognize and evaluate the TODAY() function. What am I doing wrong? I appreciate the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up a column in Excel as a check mark column? | Excel Discussion (Misc queries) | |||
| Check dates in spreadsheet against expiry years column and change colour | Excel Worksheet Functions | |||
| count the number of cells within a column with a date <= today's d | New Users to Excel | |||
| How to check for duplicates in a list of names and dates | Excel Discussion (Misc queries) | |||
| Check column of dates against todays date | Excel Worksheet Functions | |||
Linear Mode
