Forum Discussion

JennyHoA20181's avatar
JennyHoA20181
Brass 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
peteryac60's avatar
peteryac60
Iron Contributor
May 15, 2020

JennyHoA20181 

Hi Jenny

I am not sure I understand your problem completely but I've had a go at providing you a solution.

Can I assume that you are manually maintaining your data spreadsheet?

Is it the case that you want for a selected year determine the status and contract type of a particular account?

 

I attach a possible solution - but I am not 100% I understand what you need. If this is what you need then I can go through it with you in more detail. If not, if you clarify it a bit more I can have another go!

 

Hope this helps.

 

Peter

 

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

    Hi peteryac60 

     

    Thank you so much for this! This is a much better way of doing it 🙂

     

    Basically I want to COUNT the number of Agents in 2017 that had ACTIVE contracts. For this, I need to know the contract type and and status in a given year. We are so close now!

     

    So my end result would be below (using dummy numbers). So in 2017 I don;t want to count Agents that used to be ECs in 2017. In 2017 they should be under 'EC'.  So really the 16 should be 0, and EC 46 (because they were not agents in 2017).  Ignore the %s for now.


    Active Number Of ECs & Agents :

     2017 2018 2019 2020  
    Agent1615%2927%4340%5248% 
    EC4069%4171%4272%5086% 
    • peteryac60's avatar
      peteryac60
      Iron Contributor
      May 15, 2020

      JennyHoA20181 

      Hi Jenny,

       

      Sorry you have confused me a bit further!!

      Spoiler
      If you only need a 'count' table then you can do something like the table i started in the sheet COUNT - see attached?

      Is this what you are looking for?

      If not let me know and I can look again!

      regards,


      Peter







       

       

      • JennyHoA20181's avatar
        JennyHoA20181
        Brass 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!

         

Resources