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

1. ## 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!

2. ## 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

3. ## 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. ## 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
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

£1300

again is that correct?

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

Hi

Sorry yes it would be 5 not 8

6. ## 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. ## Re: Formula include if date in one cell is between dates in two others

you are welcome

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