# Excel 2007 : Today() Formula

1. ## Today() Formula

I am using the CountIfs formula, =COUNTIFS(A1:A100, "John", B1:B200,"<="&TODAY()+30).

In column A I have Names (John, Bill, etc...), In column B I have Project Deadlines as Dates. I have three cells that will contain the results of the formula (one cell for 30 days, another for 60 days and another for 90 + days). I need the formula to provide me how many deadlines John has within 30 days, 60 days, and 90 + days.

2. ## Re: Problem with Today() Formula

Assuming that formula is in C2 then
D2: =COUNTIFS(A1:A100, "John", B1:B200,"<="&TODAY()+60)-C2
E2: =COUNTIFS(A1:A100, "John", B1:B200,"<="&TODAY()+90)-C2-D2

would be one way.

3. ## Re: Problem with Today() Formula

Thank you for the quick reply, but I am getting the "#VALUE" error with my original formula.

4. ## Re: Today() Formula

Your ranges don't match - they need to be the same number of rows (you have A1:A100 but B1:B200)

5. ## Re: Today() Formula

I used those cells as an example, the ranges I am using are the same number of rows.

6. ## Re: Today() Formula

Do you have any error values in the cells?

7. ## Re: Today() Formula

No, there are no errors

8. ## Re: Today() Formula

See attached sample file.

9. ## Re: Today() Formula

Thank you that worked!!

10. ## Re: Today() Formula

I found a discrepancy. In your workbook, the formula that was used shows results of "14", to check and make sure that this number was accurate. I manually filtered "John" and counted the instances that John appeared within 30 days (2/10/2012 + 30 days = 3/10/2012). I only count, manually, 10 instances (including 2/10/12). Please advise of this discrepancy.

11. ## Re: Today() Formula

I do not see the discrepancy. TODAY()+30 = 11/3/2012 which covers rows 1:40 and John appears 14 times in those rows.

12. ## Re: Today() Formula

I apologize you are correct there are 14, the other issue is that I do not want to count the dates prior to Today(). I do want to count the past deadlines but in another cell. Again thank you for the assistance.

13. ## Re: Today() Formula

Originally Posted by Ozzie1121
the other issue is that I do not want to count the dates prior to Today()
I am now lost since that is precisely the opposite of what your question asked. As I have no idea what your data is, or what you actually want now, I'm afraid I can't help.

14. ## Re: Today() Formula

I apologize you are correct, there are 14 instances. I do not want to include any cells that are prior to Today(), there is a cell that I want to create for past due deadlines. How do I exclude the deadlines prior to Today(). Again, Thank you for your help!

15. ## Re: Today() Formula

I do not understand how your deadlines can be past due if they are not prior to today. What am I missing?

16. ## Re: Today() Formula

I apologize. If the person misses there deadline, the date will show in the past (example: 2/1/2012 is prior(past) to 2/13/2012). In the cell that I want to view from Today()+30. I do not want the past dates to be counted only future and present day dates. Does that help?

17. ## Re: Today() Formula

This can helps if you type desired result for specific person at where you expect to locate, for instance, for John, date/ count date in cell C100, and post example up.

18. ## Re: Today() Formula

I think I get it now - do you mean

=COUNTIFS(A1:A100, "John", B1:B100,"<="&TODAY()+30,B1:B100,">="&TODAY())

19. ## Re: Today() Formula

@Rory

Means count "John" overdue but not exceed 30 day? May be.

There are currently 1 users browsing this thread. (0 members and 1 guests)