• 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.

30 day periods vs months

J

j_sun

Member
Hi All,

This may be a rather vague question but I'm looking for any insight into working with 30 day cycles as compared to months. I have an event that will occur every 30 days if certain activity isn't logged. When I go to analyze the number of these events we receive by month, I run into a problem on 31 days months and February. In the months w/ 31 days, it is possible to have two events from the same customer (on the 1st and 30 days later on the 31st). This inflates the numbers in the 31 day month and deflates the number in the following month.


So, are there any formulas or tricks to coming up with 30 day periods as compared to months? i.e. - Jan 1 thru Jan 30, Jan 31 thru Mar 1 etc


I know I can go in and manually sort/organize my data into these buckets but I was hoping there was a custom list type of solution out there that would automate this for me.


Any ideas or past experience?


Thanks in advance,

Jason
 
You could quickly generate your list by typing in the date Jan 1 in A2, and then in A3 put:

=A2+30

Copy down to about A14 (not sure how you want to handle the end of the year).

This should give you the list that you need. As you didn't elaborate on what you need the dates for, I'm not if this is what you're looking for.


You might also want to check out the DAYS360 function. You could see how many whole 30-day months are between two dates via:

=INT(DAYS360(StartDate,EndDate)/30)
 
Thanks Luke. I'm trying to analyze month over month performance of eliminating/reducing these events. It makes it tough to compare apples to apples when some months have 30 and some 31 days. I picked a starting date and am just going to calculate the 30 day "buckets".

I'm kind of proud of myself. I took your formula and made a table that calculated the start and end dates of each 30 day bucket. I then used this formula to insert the range label into my data


=VLOOKUP(INT(((B4852-DATE(2011,10,1))/30)+1),N$3:O$37,2)
 
Have you looked at using 28 day cycles

There are 13 x 28 day cycles in a year
 
Hi Hui,

Thanks for the suggestion. The issue with this is that the cyclical event I'm dealing with is on a 30 day period. The first event can occur at any date if we fail to log any activity from the device in any consecutive 30 day period. Once the first event occurs, and if we still fail to receive activity from the device, the event will generate every 30 days from the immediate previous event.

I think Luke's suggestion will suffice for now.
 
You must log in or register to reply here.
Back
Top