| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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())) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting for 15th and last day of the month | Excel Worksheet Functions | |||
| If date in column A is less than 15th of month can I display next. | Excel Discussion (Misc queries) | |||
| 15th of prior month | Excel Worksheet Functions | |||
| Function that returns the month name | Excel Worksheet Functions | |||
| When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
Linear Mode
