Formula to Return a Number Driven by Date

[jmilazzo]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[joeu2004]

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

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

 

[jmilazzo]

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


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)

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


Jessie