+ Reply to Thread
Results 1 to 12 of 12

How to calculate number of days per year a customer is a member

  1. #1
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    How to calculate number of days per year a customer is a member

    Hi,
    I was wondering if anybody could help me with a formula in the attached file? I need to find out how many days per calendar year a customer is active. The membership often starts from august to august. But there are many customers who have different membership period, and they might alter from one year to another.

    Let me know if I need to describe it better.

    Thanks for the help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: How to calculate number of days per year a customer is a member

    Like this?
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,549

    Re: How to calculate number of days per year a customer is a member

    I don't understand your logic for the "should be". In your first two examples, the "should be" is very different but the dates are the same, just a year later:

    Please Login or Register  to view this content.
    Why should the first one be 138 but the second should be 365?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    Thanks Jacc and 6StringJazzer for the replies.
    I want to get the number of calendar days.
    For 2009: Customer became a member 16 August: 16 august 2009 to 31 December 2009 (138 days).
    For 2010: Customer 5 is member the entire 2010: 1 January 2010 to 15 August 2010 (from uw year 2009) and from 16 August 2010 to 31 December 2010.
    Last edited by Even; 09-20-2014 at 02:40 AM.

  5. #5
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    Anybody ???
    Pls let me know if I need to explain further.

  6. #6
    Registered User
    Join Date
    05-14-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: How to calculate number of days per year a customer is a member

    Hi mate,

    try something like this:

    Please Login or Register  to view this content.
    Last edited by RandomPezzer; 09-20-2014 at 05:42 PM.

  7. #7
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    Not quite. With this formula I get 138 days for 2010 and 2011. The customer is a member the entire 2010 and 2011, so it should be 365 days.

  8. #8
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    I thought this was a fairly easy problem???

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,549

    Re: How to calculate number of days per year a customer is a member

    Here is what I think you are saying. A customer membership starts and ends on some arbitrary days. It can ends in a different year than it starts. A new membership period begins the day after the previous one ended.

    You have a list of the Start and End dates of these membership periods. You need to determine the number of calendar days during each calendar year that the membership for that customer is in effect.

    Your example shows one customer. What does this look like for the rest of the customers? Is it always the years 2009-2014? If so, you can use the attached. Note that the formula in row 2 is one formula, the formula in row 7 is another, and the rows in between are another.

    If your customers don't have the same number of years, then you need to modify your data layout. It is a poor practice to use merged cells to contain data in records. It would be much better to put the customer number in every row, so the formulas can detect when a new customer starts.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    Yes, that is what I mean. I am attaching a new file containing more data. Thanks!
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: How to calculate number of days per year a customer is a member

    Your data is a mess. This is the best I can do with what you got.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Norway
    MS-Off Ver
    Excel in Office 365
    Posts
    147

    Re: How to calculate number of days per year a customer is a member

    Yes, it's a mess. That is why I needed help. And it looks like you helped me out - thanks a lot. Great job The data is regarding an insurance company so the information is quite substantial. I have another question related to this, but I will start a new thread. (hopefully you can help me out on that too ).

+ 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. Calculate age if there were 112 days in a year
    By army in forum Excel General
    Replies: 7
    Last Post: 12-09-2014, 04:59 PM
  2. [SOLVED] How to calculate Year, Month and Day from total number of days?
    By Indra Rai in forum Excel General
    Replies: 4
    Last Post: 06-25-2014, 02:08 PM
  3. Replies: 15
    Last Post: 06-03-2014, 02:08 PM
  4. Calculate Leave Accural In a year using staff member tennur
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2014, 06:54 AM
  5. Replies: 8
    Last Post: 05-10-2013, 05:37 AM

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