date formula returns the 15th or the end of month

[Steve]

I have tried to develop a formula that returns the 15th of the month
(if today() is <= to the 15th) and the end of the month's date (if
today() is greater than the 15th) I need it in the mm/dd/yy format.
I have tried using text and "dd" with if statements but have not
gotten it to work. I figure the end of month date could be handled
with the EOMONTH(TODAY(),0) For January the dates would be either
01/15/08 or 01/31/08,
Thanks for any help you can provide, Steve

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

=IF(DAY(TODAY())<=15,15,EOMONTH(TODAY(),0))

Rick

 

[Max]

Try:
=IF(TODAY()<=DATE(YEAR(TODAY()),MONTH(TODAY()),15),DATE(YEAR(TODAY()),MONTH(TODAY()),15),DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
Format the cell in date format as desired

 

[Steve]

Rick, the first statement returns 01/15/1900 when I format the cell
as a date in mm/dd/yy format. if the date is after the 15th then the
date displays properly as 01/31/08. How can the first statement be
changed to show the current month and year ?

Thanks Steve

 

[T. Valko]

Try this:

=IF(DAY(NOW())>15,EOMONTH(NOW(),0),TODAY()-DAY(NOW())+15)

Format as DATE

 

[Steve]

Thanks to all for the help guys
I played with Max's formula some and came up with
IF(TODAY()<=DATE(YEAR(TODAY()),MONTH(TODAY()),
15),DATE(YEAR(TODAY()),MONTH(TODAY()),15),EOMONTH(TODAY(),0))
which works as does Bill's shorter one !

Thanks very much, Happy New Year, Steve

 

[Rick Rothstein \(MVP - VB\)]

LOL... yeah, you wanted the date, didn't you? Sorry, try this...

=IF(DAY(TODAY())<=15,15+TODAY()-DAY(TODAY()),EOMONTH(TODAY(),0))

Rick


Rick, the first statement returns 01/15/1900 when I format the cell
as a date in mm/dd/yy format. if the date is after the 15th then the
date displays properly as 01/31/08. How can the first statement be
changed to show the current month and year ?

Thanks Steve

 

[Teethless mama]

=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>15),(DAY(TODAY())<16)*15)

 

[Harlan Grove]

Thanks to all for the help guys
I played with Max's formula some and came up with
IF(TODAY()<=DATE(YEAR(TODAY()),MONTH(TODAY()), 15),
DATE(YEAR(TODAY()),MONTH(TODAY()),15),EOMONTH(TODAY(),0))
which works as does Bill's shorter one ! ....

....

Well if short is the goal AND you're willing to use the ATP,

=EOMONTH(NOW()-15,0)+15*(DAY(NOW())<=15)

If you want to avoid EOMONTH, try

=TODAY()+IF(DAY(NOW())>15,32-DAY(NOW())-DAY(NOW()-DAY(NOW())+32),
15-DAY(NOW()))