• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to count number of calender days that a membership is active

Even

New Member
Hi,
I need to find out the number of days a membership is active (from start to end) for each customer. The problem is that the start and end date is not consistent. Please see the attachment. Pls let me know if I need to explain it better.
 

Attachments

  • Book1.xlsx
    13.1 KB · Views: 14
Even

firstly, Welcome to the Chandoo.org Forums

G2: =DATE(H2,12,31)-C2+1
Copy down
 
Thank you Hui.
This works when the start and end is at the beginning and end of the year. This also works at the startup of a member. But as in this case there should be 365 days in 2010: From 01.01.2010 to 15.08.2010 (from previous membership enrollment period) and then from 16.08.2010 to 31.12.2010.
2009 16-aug-2009 15-aug-2010
2010 16-aug-2010
15-aug-2011
 
Can you please post a set of data that has all possible combinations of data as your sample file didn't have that
 
Hello, thanks for the trying to help me :)
I have changed the file a little and added more members/customers. I need to find the correct number of days in column G.
Thanks!
 

Attachments

  • Book1.xlsx
    28.2 KB · Views: 8
Hi Even,

If you are looking to find the different between the start date and end date, then use formula
=DATEDIF(C2,D2,"D")

Regards,
Prasad DN
 
Even,

I'm not sure that I have correctly interpreted your question, but the attached will give the number of days between "Start" and "End" during the calendar year given in "UW year."

Hope that helps.

Regards,
Ken
 

Attachments

  • Book1_KenU.xlsx
    29.7 KB · Views: 2
Hi, let me try to explain it differently that is hopefully not so difficult:
A customer membership starts and ends on some arbitrary days. It can end in a different year than it starts. A new membership period begins the day after the previous one ended.
I have a list of the Start and End dates of these membership periods. I need to determine the number of calendar days during each calendar year that the membership for that customer is in effect.
 
Hi Even, your requirements and examples in the file do not match. Can you upload the file with expected results for say two set of customers.

Regards,
Prasad DN
 
Back
Top