Forum Discussion

JennyHoA20181's avatar
JennyHoA20181
Iron Contributor
May 15, 2020
Solved

Returning values based on Start Date / End Date in a given year

Hello everyone,   I basically have over 200 clients and they each have around 1-5 contracts with us. Each contract has a start date and end date. Each contract has a contract type  and a contract s...
excel
Excel on Mac
Formulas and Functions
Macros and VBA
JennyHoA20181's avatar
JennyHoA20181
Iron Contributor
May 15, 2020

Hi Peterpeteryac60 

 

I am confusing myself too! Ha ha.

 

So I need to count all contracts in 2017 that were Active and ECs.

 

The tricky part is determine which were active at the time and which were ECs at the time. We can't use the Contract Type and Status Fields, as they are as of today. But an Agent today, could have been an EC in 2017. And an expired contract today, could have been live in 2017.

 

I need counts in one table, but I also need a Contract Type by year field and a Status by Year field in the back end. For example:

 

Q1: In 2017 how many ECs had Active contracts where:

ECs:  Contract Type  = EC (if client has 4 contract types, we need to pick the one relating to 2017, which can only be determined by the start and end date)

Active contracts:  Status = Active  ( issue is that we need to know contract status during a given year, currently shows today’s status), so the Status may = expired where the start and end date cover 2017, but we need it to say 'Active' to change to what it was at the time.

 

Sorry if i'm not making sense!

 

peteryac60's avatar
peteryac60
Iron Contributor
May 15, 2020

JennyHoA20181 

 

Jenny - here we  go again !

 

I have adjusted the count formula which might be what you want?

I have also attach a new sheet (PIVOT) with a couple of simple pivot tables which give the same information. In the pivot table you can select from the from the drop down (e.g. contract type) to get other information.

 

BTW - a suggestion (you can ignore!) ; in your data you maybe should have the Status 2017 next to Contract Type 2017; then the 2018 columns next to each other and so on!

 

 

Peter

 

 

 

 

  • JennyHoA20181's avatar
    JennyHoA20181
    Iron Contributor
    May 15, 2020

    Hi peteryac60 

     

    Oh I am open to suggestions, so please fire away 🙂

     

    Only issue now is that it s not counting unique clients. A client name can be repeated several times, but we only want to count once in each year...see the fun I'm having? 🙂 But I really want to crack this, will solve so many problems!

    • peteryac60's avatar
      peteryac60
      Iron Contributor
      May 16, 2020

      JennyHoA20181 

      Jenny

       

      to count unique values see sheet COUNT UNIQUE which should give you what you want.

       

      If this answer your question , I'd be grateful if you could mark the answer as complete.

       

      thank you.

       

      Peter

      PS Sorry for the delay in replying - I am guessing you are in the US , I am in the UK so there is a time difference!