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.

✔ 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 2On the start date, the returned value should be 2.

re: 30 days later (10/1/2012) add two for a total of 4On 10/1/2012, the returned value should be 4.

re:

another 30 days later (11/1/2012) add two more, for a total of 630 days after 10/1/2012 is

not11/1/2012, it's 10/31/2012, therefore on 10/31/2012, the returned value should be 6.re:

and so onSince 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.

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/2012Then 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

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.

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.

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

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)

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

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.

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.

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.

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 2On the start date, the returned value should be 2.

re: 30 days later (10/1/2012) add two for a total of 4On 10/1/2012, the returned value should be 4.

re:

another 30 days later (11/1/2012) add two more, for a total of 630 days after 10/1/2012 is

not11/1/2012, it's 10/31/2012, therefore on 10/31/2012, the returned value should be 6.re:

and so onSince 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.

YES!!!!! That's it! It works!!!!! THANK YOU SO MUCH! You solved it!

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/2012I 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

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.

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.

I worked out the logic of it earlier.

I was working in the wrong direction,

Close, but no cigar.MIKE

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!

Ask Your Question

Weekly Poll

What do you think of Volkswagen's diesel settlement?

Discuss in The Lounge

Poll History