+ Reply to Thread
Results 1 to 11 of 11

Return Value if date falls within range

  1. #1
    Registered User
    Join Date
    02-28-2019
    Location
    Peterborough
    MS-Off Ver
    2013
    Posts
    94

    Return Value if date falls within range

    Hi,

    I am searching for a formula to COUNT how many times a date falls between a range, these dates are employee holiday dates and a want to show ultimately how many people will be off work on any given day. unfortunately the holiday entries are in the format of a start date and an end date, rather than a line of entry for each day they have booked.

    in column N I have a list of dates, e.g. N1=01/01/2019, N2=02/01/2019, N3=03/01/2019 etc.
    in column H I have a list of start dates, these are in random order,
    in column I i have a list of end dates,

    I want to say how many times does N1 (01/01/2019) appear in the range of H1:I100?
    How many times does N2 (02/01/2019) appear in the range of H1:I100?
    Etc. for all the dates in Column N

    Any help would be greatly appreciated as I cant quite find anything tailored specific to this request,

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Return Value if date falls within range

    please attach a sample file with data and your format.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return Value if date falls within range

    It sounds like you want:

    =COUNTIFS($H$1:$H$100,">="&N1, $I$1:$I$100,"<="&N1)
    Rory

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Return Value if date falls within range

    Try

    =SUMPRODUCT((N1>=H$1:H$100)*(N1<=I$1:i$100))
    and copy down column N

    where N is a list of calendar dates and column H is a list of start date and I is a list of end dates for each staff member
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Return Value if date falls within range

    Quote Originally Posted by rorya View Post
    It sounds like you want:

    =COUNTIFS($H$1:$H$100,">="&N1, $I$1:$I$100,"<="&N1)
    Surely thats the other way round?

    Given a calendar date N1 must be greater than H (the start date of the staff member) and less than column I (the end date) ?
    e.g.

    given date (N1) is 13/01/2019
    if staff member went on holiday on 12/01/2019 then N1 must be greater than H1 to record an absence.

  6. #6
    Registered User
    Join Date
    02-28-2019
    Location
    Peterborough
    MS-Off Ver
    2013
    Posts
    94

    Re: Return Value if date falls within range

    Hi,

    Thank you both for your replies. I have tried both of them but unable to get either to work, Below is a picture of the sheet (I don't know how to upload my excel file)

    imgur.com/qTJQ3V0

    I have manually filled in Column O to show the answer that the formula should return that im looking for. as I want it to say how many times the date in column N appears in the date range of start and end dates.

    I hope it is easy to interpret? Apologies if not

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Return Value if date falls within range

    To upload files

    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

  8. #8
    Registered User
    Join Date
    02-28-2019
    Location
    Peterborough
    MS-Off Ver
    2013
    Posts
    94

    Re: Return Value if date falls within range

    Thanks, I think I have added the attachment as per your instruction?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Return Value if date falls within range

    You've changed the range slightly so I've amended my formula.

    My formula works fine

    =SUMPRODUCT((N1>=H$2:H$100)*(N1<=I$2:I$100))

    To check it limit the range to one or two staff members so you can see the results.

  10. #10
    Registered User
    Join Date
    02-28-2019
    Location
    Peterborough
    MS-Off Ver
    2013
    Posts
    94

    Re: Return Value if date falls within range

    Excellent, works a treat thank you.

    im annoyed I could figure the correction out myself as its the same as your original solution just looking at row 2 not 1!

    Just when I think im developing my knowledge with excel I come crashing down

    I really appreciate the help so thank you!

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Return Value if date falls within range

    Quote Originally Posted by Special-K View Post
    Surely thats the other way round?
    Yep, wasn't really paying attention.

+ 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. check in which range a date falls and return a specific value
    By beltzer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2016, 04:29 PM
  2. Vlookup ID and Return Data if Date Falls Within Range
    By camalone319 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2015, 05:46 PM
  3. Search date range +/- 7 days and return date that falls within range
    By tlafferty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-13-2013, 03:26 PM
  4. [SOLVED] Return cell value if date falls within specified range
    By coza in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-10-2013, 03:58 PM
  5. Return True or False if date range falls within current month
    By kieran614 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 12:02 PM
  6. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  7. Return a specified date when it falls within a range....
    By Nokose451 in forum Excel General
    Replies: 1
    Last Post: 01-16-2006, 06:10 PM

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