Formula to Return a Number Driven by Date

  • Thread starter Thread starter jmilazzo
  • Start date Start date
J

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
 
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.
Click to expand...

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
 
=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.
 
Use Dave's formula... I screwed up the part about crossing from Dec to Jan.

Rick
 
=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
 
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
Click to expand...

=mod(month(A1)+5, 12) + 1
and then multiples that number by what is already in the cell.
Click to expand...

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)
 
=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)
Click to expand...

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


Jessie
 
Back
Top