Solved Count by 2 for every 30 days

December 5, 2012 at 08:09:23
Specs: Windows 7
I am tring to find an excel formula that will allow me to count by 2 for every 30 days from a given start date.

Ex: start date 9/1/2012, count 2, 30 days later (10/1/2012) add two for a total of 4, another 30 days later (11/1/2012) add two more, for a total of 6, and so on.


See More: Count by 2 for every 30 days

Report •


✔ Best Answer
December 12, 2012 at 08:28:03
Let's first review what you said in your OP:

Ex: start date 9/1/2012, count 2, 30 days later (10/1/2012) add two for a total of 4, another 30 days later (11/1/2012) add two more, for a total of 6, and so on.

re: start date 9/1/2012, count 2

On the start date, the returned value should be 2.

re: 30 days later (10/1/2012) add two for a total of 4

On 10/1/2012, the returned value should be 4.

re: another 30 days later (11/1/2012) add two more, for a total of 6

30 days after 10/1/2012 is not 11/1/2012, it's 10/31/2012, therefore on 10/31/2012, the returned value should be 6.

re: and so on

Since the date of this post is 12/12/2012, it is at least 30 days after 10/31/2012, but not 60 days after 10/31/2012 so the returned value should be 8.

This forumla will return the values listed above on the dates listed:

=2+(2*(INT((TODAY()-A1)/30)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 5, 2012 at 10:06:59
Not sure you can what you want with just formulas.

How is your data set up?

If it looks like this:

       A            B
1)  9/4/2012
2)  9/5/2012
3)  9/6/2012        2
4)  9/7/2012
5)  9/8/2012

Then in cell B1 enter this formula and drag down:

=IF(MOD(DATEDIF(A1,TODAY(),"D"),30)=0,2,"")

You will then need to do some type of =SUM() on column B
See if that works for you.

MIKE

http://www.skeptic.com/


Report •

#2
December 5, 2012 at 10:07:12
It is not clear to me what you are trying to do. I don't know what you mean by "count 2" for each date. Where would the 2, 4, 6, etc. be placed?

We need a clearer explanation of what you are trying to do before we can help.

BTW 30 days after 10/1/2012 is 10/31/2012, not 11/1/2012.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
December 5, 2012 at 11:53:58
Essentially, every 30 days from the start date you will earn 2 additional points. I am looking for a formula that can calculate (and continue to calculate) how many points have accumilated from/since that start date. That's why I said count by two. Sorry for the confusion.

Report •

Related Solutions

#4
December 5, 2012 at 12:22:57
Try this:

With your Initial Start Date in cell A1: 09/06/2012

Enter this formula in cell B1:

=IF(MOD(DATEDIF(A1,TODAY(),"D"),30)=0,(DATEDIF(A1,TODAY(),"D")/30)*2,"")

Just remember we are calculating on 30 Days, NOT by months.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#5
December 10, 2012 at 17:45:00
I'm not clear on how you want to go about this, but another way might be:

      A                 B
1)   start date    extra points
2)   1/1/12        =INT((TODAY()-A2)/30)      


Report •

#6
December 12, 2012 at 05:36:26
mmcconaghy - so far, I would say you've gotten the closest but it still didn't seem to work. When I copied and pasted your forumla it just made B1 a blank cell. But if i tried removing the (,"") from the end of the formula, B1 then became (FALSE).

Report •

#7
December 12, 2012 at 05:43:34
paul1149, thanks but the idea is to have the formula return a progressively increasing count without having to "maintain" a spreadsheet and plug in a new date in column A each time. I tried your formula but it yeilded a hight number so I don't think it's counting by two for each 30 days.

Report •

#8
December 12, 2012 at 06:25:50
Could you please explain this:

without having to "maintain" a spreadsheet and plug in a new date in column A each time.

Are you saying that you don't have a list of dates in Column A?

Please click on the following line, read the instructions found via that link and then post an example of your data so we know what we are working with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
December 12, 2012 at 06:31:07
Correct, I do not have a list of dates in Column A.
In A1 I have a start day (i.e. 9/1/2012) and in B1 I'd like the formula to calculate (and continue to calculate) how many points have accumilated from/since that start date. The points accumilate by 2 for every 30 days.

Report •

#10
December 12, 2012 at 08:28:03
✔ Best Answer
Let's first review what you said in your OP:

Ex: start date 9/1/2012, count 2, 30 days later (10/1/2012) add two for a total of 4, another 30 days later (11/1/2012) add two more, for a total of 6, and so on.

re: start date 9/1/2012, count 2

On the start date, the returned value should be 2.

re: 30 days later (10/1/2012) add two for a total of 4

On 10/1/2012, the returned value should be 4.

re: another 30 days later (11/1/2012) add two more, for a total of 6

30 days after 10/1/2012 is not 11/1/2012, it's 10/31/2012, therefore on 10/31/2012, the returned value should be 6.

re: and so on

Since the date of this post is 12/12/2012, it is at least 30 days after 10/31/2012, but not 60 days after 10/31/2012 so the returned value should be 8.

This forumla will return the values listed above on the dates listed:

=2+(2*(INT((TODAY()-A1)/30)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
December 12, 2012 at 08:39:15
YES!!!!! That's it! It works!!!!! THANK YOU SO MUCH! You solved it!

Report •

#12
December 12, 2012 at 08:51:54
I'm not sure that the formula works the way you think.

Using DerbyDad03 formula:

=2+(2*(INT((TODAY()-A1)/30)))

And the starting date of 9/1/2012

I get this:

       A          B
 1) 9/1/2012      8
 2) 9/2/2012      8
 3) 9/3/2012      8
 4) 9/4/2012      8
 5) 9/5/2012      8
 6) 9/6/2012      8
 7) 9/7/2012      8
 8) 9/8/2012      8
 9) 9/9/2012      8
10) 9/10/2012     8
11) 9/11/2012     8
12) 9/12/2012     8
13) 9/13/2012     8
14) 9/14/2012     6 <<<
15) 9/15/2012     6 <<<

MIKE

http://www.skeptic.com/


Report •

#13
December 12, 2012 at 08:57:06
Glad I could help.

I think the issue was that none of us knew exactly how your spreadsheet was set up. I think we all assumed that you had a list of dates and you wanted the values returned next to those dates.

Please take this advice in the spirit that it is offered:

Since we can't see your spreadsheet from where we're sitting, you should give us as much detail as possible right up front so that we work on the exact problem, not on an assumed problem. You should compose your questions as if you are one of us, sitting out here not knowing anything about how your sheet is set up or is being used.

Have a happy holiday season!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
December 12, 2012 at 11:54:06
Mike,

That's exactly what you should be getting.

If the start date is 9/14/2012, then that is less than 90 days before TODAY(). Since the 2 points is awarded every 30 days, the award would be 2 to start, 2 for crossing the 30 day threshold and 2 for crossing the 60 day threshold.

2+2+2=6

For a 9/14/2012 start date, we have to wait until 12/13/2012 to reach the 90 day threshold, at which time another 2 points will be awarded, totaling 8.

This can be tested by putting a start date in A1 and setting your system date to various test dates or by putting test dates in e.g. C1 and using this:

=2+(2*(INT((C1-A1)/30)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
December 12, 2012 at 12:12:12
I worked out the logic of it earlier.
I was working in the wrong direction,
Close, but no cigar.

MIKE

http://www.skeptic.com/


Report •

#16
December 12, 2012 at 12:22:16
DerbyDad03,
No offense taken. I've never logged onto a site to ask a question like this before, and I can completely understand where I did not offer enough information. Now that I know of such a site, I will most definitely share this knowledge with my contacts and remember to paint a bigger and clearer picture for the next time. :-) Thanks again for all your help and Happy Holidays to all!

Report •


Ask Question