+ Reply to Thread
Results 1 to 5 of 5

Formula to count number of times a date is 15 or 28 in a date range

  1. #1
    Registered User
    Join Date
    03-03-2017
    Location
    Mililani, HI
    MS-Off Ver
    MS Office 2010
    Posts
    1

    Formula to count number of times a date is 15 or 28 in a date range

    We're looking for a formula to count the number of times at date is either the 15th or the 28th between two dates in excel.

    The goal of the field is to show the number of Semi Monthly Checks in a date range.

    For example: from 1/1/2017 - 3/3/2017, there are 4 Semi-Monthly Checks.

    Semi Monthly means they get paid twice a month. Usually the 15th and end of month.

    I don't know much about VBA or Excel formulas.

    Can anyone assist?

    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,440

    Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

    You could do a text or OR(DAY(cell-with-date=15),DAY(cell-with-date=28)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

    See if this does what you want.

    Entered in C1 as in the below and copied down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    1
    1/1/2017
    3/3/2017
    4
    =SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1)))=15)+(ROW(INDIRECT(A1&":"&B1))=EOMONTH(ROW(INDIRECT(A1&":"&B1)),0)))
    2
    4/1/2017
    7/14/2017
    6
    3
    7/1/2017
    7/13/2017
    0
    Dave

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Need help with a formula that counts number of times a date is 15 or 28 in a date rang

    Hello Dave,

    That's an excellent approach!

    As the last day of the month is obviously followed by the 1st of the next month you could also count the numbers of 15ths + end of months by counting whenever the next day is 16 or 1, e.g.

    =SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1))+1)={16,1})+0)

    ....or if it's actualy 15th and 28th to count

    =SUMPRODUCT((DAY(ROW(INDIRECT(A1&":"&B1)))={15,28})+0)
    Audere est facere

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Formula to count number of times a date is 15 or 28 in a date range

    @ daddylonglegs

    I just slapped myself in astonishment!

    The simplicity! I love it!

    Thanks for the lesson and the back up.
    Last edited by FlameRetired; 03-03-2017 at 08:34 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Plot dates from given date from give number of times in a vertical date range.
    By Sekars in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-09-2016, 01:37 AM
  2. Replies: 5
    Last Post: 07-18-2016, 04:12 PM
  3. [SOLVED] Count the number of times a date appears in a range
    By uhtfgy in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-12-2015, 05:53 AM
  4. Count number of times a specific date happens between two dates
    By Interrogo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-16-2015, 10:02 PM
  5. [SOLVED] Count the number of times todays date occurs
    By tylerf in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-29-2013, 01:51 PM
  6. Replies: 3
    Last Post: 08-22-2011, 11:58 AM
  7. How to count the number of times a date appears in a column
    By smellsgood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2010, 07:09 AM

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