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 status- i.e in 2017 a client may have a contract type 'EC' and in 2018 switched to 'Agent'. In a given year, I want to report the correct Contract Type. The Contract Type is dynamic - i.e reflects the latest Contract Type as of today -  in excel I want it to reflect the Contract Type it was in a given year.

 

same for Contract Status. A Contract may have a contract Status of "Expired" in 2020, but I want to show that it was "Active" in 2017, if the start and end dates indicate there was a contract that year.

 

I have attached the excel with the issues - and my attempt at resolving the issue! I have created a new column for each year, but there may be a better way of doing this.

 

Thanks

 

Jenny

excel
Excel on Mac
Formulas and Functions
Macros and VBA

26 Replies

Sort By
  • 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







         

         

Resources