Valid dates, and dynamic row counting
Posted by Ryan on July 23, 2001 6:55 AM
I need to figure out if the values in cells A1 and B1 are a valid date/time (07/20/01 7:59:25) (07/20/01 8:10:59) if they are I need to subtrack the two to get the time differnce in C1. If they either one is not a valid date then I need to populate the cell C1 with a '0'
I also need to (from another sheet) count how many rows are populated with the results of the above that are not equal to '0'.
Can you give me some pointers on what I need to do here?
Posted by Aladin Akyurek on July 23, 2001 7:28 AM
In C1 enter: =IF(AND(ISNUMBER(YEAR(A1)),ISNUMBER(YEAR(B1))),B1+(B1 < A1)-A1,0) [ copy down this formula as far as needed. ]
Since you want to return 0, instead of say a blank, when A1 or B1 or both not dates, you have to avoid time formatting C from C1 on.
On the other sheet, assuming that the foregoing is say on Sheet1, use
=COUNTIF(Sheet1!C1:C50,"<>0")
If not a problem for you, I'd suggest that the IF-formual returns blank ("") instead of 0. The last formula then becomes:
=COUNT(Sheet1!C1:C50)
Hope this is what you wanted to have.
Aladin
==========
