+ Reply to Thread
Results 1 to 7 of 7

Formula include if date in one cell is between dates in two others

  1. #1
    Registered User
    Join Date
    12-17-2015
    Location
    Penrith
    MS-Off Ver
    2007
    Posts
    27

    Formula include if date in one cell is between dates in two others

    Hi,

    I'm trying to do a formula and keep getting stuck on it and getting to my wits end.

    There's actually two, a sumif and a countif (I think at least)

    One is a countif - to count how many clients are included in that week based on whether that week is after their start date but doesn't have a value in 'end date' meaning they were a current client in that week (but doesn't change the info for that week once they do have a value when they leave, just not included from that week on) and have a certain funding type in the next column

    The other is a sumif of the weekly amounts if that week they are an existing client and have a certain funding type in the next column.

    I have attached a spreadsheet example to show, on 3 tabs

    I would really appreciate any help at all as this is out of my depth, I can do formulas based on say 2 conditions, but where it's based on conditions of one cell containing text, and another between two other values and then counting or summing a third is just throwing me off completely!
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    5,808

    Re: Formula include if date in one cell is between dates in two others

    can you give some examples of expected results - would help understand more
    anyway
    One is a countif - to count how many clients are included in that week based on whether that week is after their start date but doesn't have a value in 'end date' meaning they were a current client in that week
    for example client E stats on a Saturday 1/2/20 , so would that be included in your weeks commencing 27th jan OR not until WC 3rd Feb

    also you have ADAM & DC sheets , is that also a criteria , just to use that paid by

    Client G ENDS ON 20 FEB - Which is thursday , are they included in week 17th Feb , or because it has an end date - ignore

    as mentioned , io think i need clearer rules
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    12-17-2015
    Location
    Penrith
    MS-Off Ver
    2007
    Posts
    27

    Re: Formula include if date in one cell is between dates in two others

    Hi,

    for example client E stats on a Saturday 1/2/20 , so would that be included in your weeks commencing 27th jan OR not until WC 3rd Feb
    -included in any partial week at start or end of service

    also you have ADAM & DC sheets , is that also a criteria , just to use that paid by
    would be a criteria, so the same formulas but including the different words, results returned in the tables on the relevant sheets

    Client G ENDS ON 20 FEB - Which is thursday , are they included in week 17th Feb , or because it has an end date - ignore
    ignore in the weeks after the end date


    Expected results;
    So sheet 1 is the client list to draw the data from
    e.g. 1 on the 'Adam' sheet on the row for w/c 02/03/20 I'm trying to end up with a total count of existing clients in that week that have 'adam' as their funding (based on sheet one, is their start date before this week and they don't have an end date, hence they are a current client at this point.)
    then also a total amount based on the same parameters but summing up the weekly amount rather than just adding the number of clients. e.g. that week we have 8 active clients funded by 'adam', and their weekly totals sum up to 2,000 - this is the total we expect from 'adam' funder that week

    Then the same on the DC sheet, except counting/summing if the funder is 'dc'

    What I'm finding hardest is the bit where it will include a client on a week with a start date before it and no end date, but not include the client in weeks after an end date is entered.


    as mentioned , io think i need clearer rules

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    5,808

    Re: Formula include if date in one cell is between dates in two others

    you only have 5 clients without an END date in your sample - SO how is that
    that week we have 8 active clients funded by 'adam',
    AND in the total sample you only have 5 clients with ADAM so again how do you count 8 ???
    still confused sorry

    SO to count the number of clients with a START DATE before the WEEK starting A3 on Adam sheet and without an ENDdate would be
    =COUNTIFS('Funded clients'!B:B,"ADAM",'Funded clients'!D:D,"<"&A3,'Funded clients'!E:E,"")
    and thats 3
    Then you can SUMIFS ()
    BUT before doing to much more is that number correct for ADAM W/C 2nd March 2020

    FOR DC Portal Active = 2
    =COUNTIFS('Funded clients'!B:B,"DC Portal",'Funded clients'!D:D,"<"&A3,'Funded clients'!E:E,"")

    For SUM
    =SUMIFS('Funded clients'!C:C,'Funded clients'!B:B,"Adam",'Funded clients'!D:D,"<"&A3,'Funded clients'!E:E,"")

    1300

    again is that correct?

  5. #5
    Registered User
    Join Date
    12-17-2015
    Location
    Penrith
    MS-Off Ver
    2007
    Posts
    27

    Re: Formula include if date in one cell is between dates in two others

    Hi

    Sorry yes it would be 5 not 8

  6. #6
    Registered User
    Join Date
    12-17-2015
    Location
    Penrith
    MS-Off Ver
    2007
    Posts
    27

    Re: Formula include if date in one cell is between dates in two others

    Hi I've just run that and yes that's the results I was after, thank you I was completely confusing myself!

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    5,808

    Re: Formula include if date in one cell is between dates in two others

    you are welcome

+ 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] date not include sunday,saturday and not include red date in tabel
    By alamsyah in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-19-2019, 11:55 AM
  2. Number of days between two dates but include current date in the count
    By Jimmib78 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2015, 11:59 AM
  3. Replies: 0
    Last Post: 10-27-2011, 09:14 AM
  4. Replies: 3
    Last Post: 10-03-2011, 03:19 PM
  5. Replies: 2
    Last Post: 07-07-2011, 06:26 AM
  6. subtract dates to include start date
    By graemel in forum Excel General
    Replies: 2
    Last Post: 08-26-2010, 06:21 AM
  7. How do include Dates in a formula?
    By Grisha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2006, 05:30 PM

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