Formula to Return a Number Driven by Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jmilazzo@gmail.com - 06 Jul 2007 16:48 GMT

Hi

I am trying to write a formula that will look at todays date and if it
falls in July to return a 1, if it falls within August to return a 2,
if it falls within September a 3, October a 4, November a 5, December
a 6, January a 7, February an 8, March a 9, April a 10, May an 11 and
June a 12 and then multiples that number by what is already in the
cell.

    A    B    C
1    7/6/2007
2
3
4    $833.00

Thanks
Jessie

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jul 2007 17:02 GMT

> I am trying to write a formula that will look at todays date and if it
> falls in July to return a 1, if it falls within August to return a 2,
> if it falls within September a 3, October a 4, November a 5, December
> a 6, January a 7, February an 8, March a 9, April a 10, May an 11 and
> June a 12 and then multiples that number by what is already in the
> cell.

You can calculate the number you want using this formula

=MONTH(TODAY())-6

For the multiplication part, I am not sure what you mean by "what is already
in the cell". Anyway, if the value you want to multiply by is in, say, A4,
then the formula you want should be...

=A4*(MONTH(TODAY())-6)

Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jul 2007 18:17 GMT

Use Dave's formula... I screwed up the part about crossing from Dec to Jan.

Rick

>> I am trying to write a formula that will look at todays date and if it
>> falls in July to return a 1, if it falls within August to return a 2,
[quoted text clipped - 14 lines]
>
> Rick

Reply to this Message

Dave Peterson - 06 Jul 2007 18:08 GMT

=MOD(MONTH(Today())-7,12)+1

You can either use two cells--one for the amount and one for that number
or you can add that to the value in the cell

=833*MOD(MONTH(Today())-7,12)+1

It would make more sense to me to use multiple cells.

> Hi
>
[quoted text clipped - 13 lines]
> Thanks
> Jessie

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jul 2007 18:23 GMT

> =MOD(MONTH(Today())-7,12)+1

Or, alternately...

=MOD(MONTH(A1)+5,12)+1

(in case you only like working with positive numbers<g>)

Rick

Reply to this Message

joeu2004 - 06 Jul 2007 18:24 GMT

On Jul 6, 8:48 am, jmila...@gmail.com wrote:

=mod(month(A1)+5, 12) + 1

> and then multiples that number by what is already in the cell.

Well, you cannot literally multiply that by "what is already in the
cell". That would be a circular formula. But you __can__ do the
multiplication in the formula in that cell or in another cell. For
example:

=833 * (mod(month(A1)+5,12) + 1)

=A4 * (mod(month(A1)+5,12) + 1)

Reply to this Message

jmilazzo@gmail.com - 06 Jul 2007 19:09 GMT

> On Jul 6, 8:48 am, jmila...@gmail.com wrote:
>
[quoted text clipped - 16 lines]
>
> =A4 * (mod(month(A1)+5,12) + 1)

Thank you all so so very much! I was stuck and you all helped me so
much! Thank you again.

Jessie

Reply to this Message