+ Reply to Thread
Results 1 to 4 of 4

Show value if between multiple date ranges

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    SF
    MS-Off Ver
    Excel 365 for Mac
    Posts
    2

    Post Show value if between multiple date ranges

    Hello - I'm trying to find an elegant formula to show a value if it falls between one of multiple date ranges. See the attached Excel.

    I have a number of clients, each of whom has multiple periods where users can sign up - see the first tab. In the second tab, I want to show the sign-ups per day for a given day if that day falls within one of the multiple date ranges in the first tab.

    I know I could use an IF/AND formula for each date range, but I will need to easily be able to add date ranges, and so I'm wondering if there is a more elegant way of doing it.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Show value if between multiple date ranges

    Please try at B3:B26

    =COUNTIFS('Client sign up periods'!$E3:$O3,"<="&B$2:AR$2,'Client sign up periods'!$F3:$P3,">="&B$2:AR$2,'Client sign up periods'!$F$2:$P$2,"END")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    SF
    MS-Off Ver
    Excel 365 for Mac
    Posts
    2

    Re: Show value if between multiple date ranges

    Hello Bo_Ry.

    I would like each cell in the second tab to show the signs up per day (column B in the first tab). This isn't the case in your version.

    Can you explain the logic behind your formula - I don't quite understand it, so am struggling to replicate it in the file with my real data. Thank you!
    Last edited by msmsmsms; 04-20-2020 at 09:17 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,421

    Re: Show value if between multiple date ranges

    Try modifying Bo_Ry's formula to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Show Values using IF function on Multiple Ranges
    By vikrampnz in forum Excel General
    Replies: 5
    Last Post: 09-06-2018, 12:48 PM
  2. Hide or show multiple named ranges of rows
    By missbogota in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2017, 11:04 AM
  3. Replies: 2
    Last Post: 08-16-2013, 10:18 AM
  4. pulling multiple date ranges from 1 cell into multiple rows?
    By 13lack13lade in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2013, 11:50 PM
  5. [SOLVED] Counting the number of times a date occurs in multiple start and end date ranges.
    By Grizz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-05-2013, 04:41 PM
  6. Analyse date frequency cross-referencing multiple date ranges
    By globalpontoon in forum Excel General
    Replies: 2
    Last Post: 05-15-2012, 04:24 PM
  7. Replies: 8
    Last Post: 01-07-2011, 07:36 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