Date formula required

[Compass Rose]

I need a formula that will enter a date in a cell based on today's date. I'll
explain.

If today's date is from the 1st of the month to the 15th of the month, I
need the 25th day of the previous month in the cell. If the date is from the
16th of the month to the last day of the month, I need the 25th day of the
present month to appear in the cell.

Examples Today's date Cell date
11/16/2006 11/25/2006
12/5/2006 11/25/2006
1/13/2007 12/25/2006

I hope I have explained clearly.

TIA
David

 

[Allllen]

=IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH(A1)-1,25),DATE(YEAR(A1),MONTH(A1),25))

for a value in cell A1.

 

[Allllen]

....don't forget to format it as a date (format > cell > number > date)

 

[David Biddulph]

Or, slightly shorter,
=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1)-1,25),DATE(YEAR(A1),MONTH(A1),25))

 

[JE McGimpsey]

Or even shorter:

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<=15),25)

 

[Compass Rose]

Thanks Allllen, but your formula is based on the assumption that today's date
is in A1, which isn't the case. Today's date doesn't appear in any of the
cells of the worksheet, so I would like the formula to reference TODAY() to
arrive at the correct date, not reference a cell. I could put today's date in
a cell and then hide it, but I would like to avoid that if I can.

David

 

[Compass Rose]

Figured it out, I think:

=DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25)

David

 

[JE McGimpsey]

One way:

=DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25)

 

[Compass Rose]

Thanks for the help, JE.

I hate to get picky, but is there a way to format the cell so that the month
appears in upper case, rather than lower?

David

 

[JE McGimpsey]

Format, no (other than using a font with only upper case characters),
but you could us

=UPPER(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25))

 

[Compass Rose]

Thanks JE. I tried your suggestion but it returned the serial value of the
date. I tweeked to

=UPPER(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25),"MMMM
d, yyyy")

which did the trick.

David