| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding the last day of the month
I have a spreadsheet that has a date column listing dates going back 3 years,
each month has about 50 line items with different days of the month, it there a way to create a new column that lists the last day of the month for each line item? For example if the date was August 9, 2007 the formula would return August 31,2007. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding the last day of the month
Assume the date is in A3
=DATE(YEAR(A3),MONTH(A3)+1,) will return the date of the last day of the month for the date in A3 -- Regards, Peo Sjoblom "Charles" wrote in message ... I have a spreadsheet that has a date column listing dates going back 3 years, each month has about 50 line items with different days of the month, it there a way to create a new column that lists the last day of the month for each line item? For example if the date was August 9, 2007 the formula would return August 31,2007. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding the last day of the month
With
A2: (a date) If you have the Analysis ToolPak installed: =EOMONTH(A2,0) If you don't....then try this: =DATE(YEAR(A2),MONTH(A2)+1,0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Charles" wrote in message ... I have a spreadsheet that has a date column listing dates going back 3 years, each month has about 50 line items with different days of the month, it there a way to create a new column that lists the last day of the month for each line item? For example if the date was August 9, 2007 the formula would return August 31,2007. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding the last day of the month
Great solutions!! Thanks for the help...
"Ron Coderre" wrote: With A2: (a date) If you have the Analysis ToolPak installed: =EOMONTH(A2,0) If you don't....then try this: =DATE(YEAR(A2),MONTH(A2)+1,0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Charles" wrote in message ... I have a spreadsheet that has a date column listing dates going back 3 years, each month has about 50 line items with different days of the month, it there a way to create a new column that lists the last day of the month for each line item? For example if the date was August 9, 2007 the formula would return August 31,2007. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
finding the last day of the month
I'm glad I could help, Charles......Thanks for the feedback.
Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Charles" wrote in message ... Great solutions!! Thanks for the help... "Ron Coderre" wrote: With A2: (a date) If you have the Analysis ToolPak installed: =EOMONTH(A2,0) If you don't....then try this: =DATE(YEAR(A2),MONTH(A2)+1,0) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Charles" wrote in message ... I have a spreadsheet that has a date column listing dates going back 3 years, each month has about 50 line items with different days of the month, it there a way to create a new column that lists the last day of the month for each line item? For example if the date was August 9, 2007 the formula would return August 31,2007. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help finding the date of the last Saturday of a given month | Excel Worksheet Functions | |||
| Finding Particular Day of Month | Excel Worksheet Functions | |||
| Finding the last day of a month | Excel Discussion (Misc queries) | |||
| Finding the Sum of value each month | Excel Discussion (Misc queries) | |||
| Finding min and max date of a given month | Excel Worksheet Functions | |||
Linear Mode
