date formula returns the 15th or the end of month
 

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

date formula returns the 15th or the end of month
 

Give this a try...

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

Rick


"Steve" wrote in message
...
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

date formula returns the 15th or the end of month
 

Try:
=IF(TODAY()<=DATE(YEAR(TODAY()),MONTH(TODAY()),15) ,DATE(YEAR(TODAY()),MONTH(TODAY()),15),DATE(YEAR(T ODAY()),MONTH(TODAY())+1,0))
Format the cell in date format as desired
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote in message
...
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

date formula returns the 15th or the end of month
 

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



On Jan 1, 9:32*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Give this a try...

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

Rick

"Steve" wrote in message

...



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- Hide quoted text -

- Show quoted text -

date formula returns the 15th or the end of month
 

Try this:

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

Format as DATE

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

date formula returns the 15th or the end of month
 

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










On Jan 1, 9:54*pm, "T. Valko" wrote:
Try this:

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

Format as DATE

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...



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- Hide quoted text -

- Show quoted text -

date formula returns the 15th or the end of month
 

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

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

Rick


"Steve" wrote in message
...
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



On Jan 1, 9:32 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Give this a try...

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

Rick

"Steve" wrote in message

...



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- Hide quoted text -

- Show quoted text -

date formula returns the 15th or the end of month
 

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


"Steve" wrote:

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

date formula returns the 15th or the end of month
 

Steve wrote...
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(TOD AY(),0))
which works as does Bill's shorter one !
....
"T. Valko" wrote:
....
=IF(DAY(NOW())15,EOMONTH(NOW(),0),TODAY()-DAY(NOW())+15)
....

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