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

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

Like this?

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

Why should the first one be 138 but the second should be 365?

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

5. ## 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. ## Re: How to calculate number of days per year a customer is a member

Hi mate,

try something like this:

7. ## 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. ## Re: How to calculate number of days per year a customer is a member

I thought this was a fairly easy problem???

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

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

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

12. ## 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 ).

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

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