+ Reply to Thread
Results 1 to 14 of 14

Count the number of times todays date occurs

  1. 04-26-2013, 04:00 PM #1
    tylerf
    tylerf is offline
    Registered User
    Join Date
    03-07-2013
    Location
    San Antonio
    MS-Off Ver
    Excel 2010
    Posts
    45

    Count the number of times todays date occurs

    Good afternoon,

    I need help coming up with 2 formulas. THe first would count the number of times todays date occurs within a give range of cells. The second would count the nmber of time a date within the past week occured in a range of cells.

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 04-26-2013, 04:03 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    For the count of today's date:

    =COUNTIF(A1:A10,TODAY())

    Adjust the range to suit.

    For your other question define "past week".
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 04-26-2013, 04:04 PM #3
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count the number of times todays date occurs

    Try these

    =COUNTIF(A1:A100,TODAY())

    and for last week, assuming there are no future dates in the range..
    =COUNTIF(A1:A100,">="&TODAY()-7)

    If there ARE future dates in the range
    =COUNTIFS(A1:A100,">="&TODAY()-7,A1:A100,"<="&TODAY())
    Reply With QuoteRegister To Reply
  4. 04-26-2013, 04:37 PM #4
    tylerf
    tylerf is offline
    Registered User
    Join Date
    03-07-2013
    Location
    San Antonio
    MS-Off Ver
    Excel 2010
    Posts
    45

    Smile Re: Count the number of times todays date occurs

    thank you. The formula for the week would need to reflect dates between a monday and friday. meaning if it was monday, it would reflect anything that day or 4 days past, but if it was wednesday it would reflect 2 days prior, same day, and two days past.

    Thanks,
    Reply With QuoteRegister To Reply
  5. 04-26-2013, 05:36 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    Let's try this...

    Today is 4/26/2013.

    Define the "past week" based on today's date. Use dates to define the past week.
    Reply With QuoteRegister To Reply
  6. 04-29-2013, 09:22 AM #6
    tylerf
    tylerf is offline
    Registered User
    Join Date
    03-07-2013
    Location
    San Antonio
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Count the number of times todays date occurs

    good morning,

    The "current week" for 4/26 would be 4/22-4/26.

    Does that help? Thanks for the assistance!
    Reply With QuoteRegister To Reply
  7. 04-29-2013, 09:32 AM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    Ok, so the past week would be 4/15/2013 to 4/19/2013, right?
    Reply With QuoteRegister To Reply
  8. 04-29-2013, 10:11 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    Quote Originally Posted by Tony Valko View Post
    Ok, so the past week would be 4/15/2013 to 4/19/2013, right?
    Let's assume that's correct.

    Try this...

    A2:A25 = dates

    =COUNTIFS(A2:A25,">="&TODAY()-WEEKDAY(NOW(),2)+1-7,A2:A25,"<="&TODAY()-WEEKDAY(NOW(),2)+1-7+4)

    That will count the Monday thru Friday dates of the previous week based on today's date.
    Reply With QuoteRegister To Reply
  9. 04-29-2013, 10:34 AM #9
    tylerf
    tylerf is offline
    Registered User
    Join Date
    03-07-2013
    Location
    San Antonio
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Count the number of times todays date occurs

    thanks for the help. Perhaps I was a little unclear. I would be looknig for dates that occur in the current week. Meaning if today was 4/25, I would need to see the number of dates occuring between 4/22 and 4/26. I would prefer for the formula to work regardless of what week it is, meaning it would always count the current work week so that the formula does not have to be changed.

    I hope I am making sense!
    Reply With QuoteRegister To Reply
  10. 04-29-2013, 10:46 AM #10
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    This will count the Monday thru Friday dates of the current week:

    =COUNTIFS(A2:A25,">="&TODAY()-WEEKDAY(NOW(),2)+1,A2:A25,"<="&TODAY()-WEEKDAY(NOW(),2)+5)
    Reply With QuoteRegister To Reply
  11. 04-29-2013, 10:50 AM #11
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Count the number of times todays date occurs

    Here's a slightly different approach using SUMPRODUCT

    =SUMPRODUCT((ABS(TODAY()-WEEKDAY(TODAY(),3)+2-A2:A25)<=2)+0)
    Audere est facere
    Reply With QuoteRegister To Reply
  12. 04-29-2013, 10:57 AM #12
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    Just so there's no confusion...

    In your original post you said:

    ...count the nmber of time a date within the past week occured in a range of cells.
    That's why I was trying to get the past week clearly defined.
    Reply With QuoteRegister To Reply
  13. 04-29-2013, 11:40 AM #13
    tylerf
    tylerf is offline
    Registered User
    Join Date
    03-07-2013
    Location
    San Antonio
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Count the number of times todays date occurs

    thank you so much for the help. THis last formula does exactly what I was wanting. Sorry i was not clear with what i was wanting. Lesson learned for next time to be more specific.

    Thanks again!
    Reply With QuoteRegister To Reply
  14. 04-29-2013, 01:51 PM #14
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count the number of times todays date occurs

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1