+ Reply to Thread
Results 1 to 19 of 19

Excel 2007 : Today() Formula

  1. #1
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    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.

    Your help is very appreciated!!

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    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.
    Good luck.

  3. #3
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Problem with Today() Formula

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

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    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. #5
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Today() Formula

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

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Today() Formula

    Do you have any error values in the cells?

  7. #7
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Today() Formula

    No, there are no errors

  8. #8
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Today() Formula

    See attached sample file.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Today() Formula

    Thank you that worked!!

  10. #10
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    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. #12
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    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. #13
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Today() Formula

    Quote Originally Posted by Ozzie1121 View Post
    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. #14
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. #15
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    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. #16
    Registered User
    Join Date
    02-09-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    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.
    Quang PT

  18. #18
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Today() Formula

    I think I get it now - do you mean

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

  19. #19
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Today() Formula

    @Rory

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1