Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
If cell "a1" has a date, how could I check whether the date is the end of the
month and if not change it to the end of the month? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
=EOMONTH(A1,0)
Format as any type of date you want. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Stratuser" wrote: If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
Here is another way which doesn't require the Analysis ToolPak...
=IF(DAY(A1+1)1,DATE(YEAR(A1),MONTH(A1)+1,0),A1) -- Rick (MVP - Excel) "Stratuser" wrote in message ... If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
I got hung up on your "how do you test" question that I didn't pay attention
to what you ultimately wanted. This simpler formula will yield the end of the month date for the date specified in A1... =DATE(YEAR(A8),MONTH(A8)+1,0) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is another way which doesn't require the Analysis ToolPak... =IF(DAY(A1+1)1,DATE(YEAR(A1),MONTH(A1)+1,0),A1) -- Rick (MVP - Excel) "Stratuser" wrote in message ... If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
Is this a VB question by any chance? And if so, did you want to physically
change the date in A1 from whatever it is to the end of its month (thus losing whatever was in the cell to begin with)? If the answer to both of these questions are "Yes", then try this macro out... Sub ChangeDateToEndOfMonth() With Range("A1") .Value = DateSerial(Year(.Value), Month(.Value) + 1, 0) End With End Sub -- Rick (MVP - Excel) "Stratuser" wrote in message ... If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
I have a very old Excel program that imports data with dates, but I have
recently started working in Excel 2007. I'm trying to check each of the dates in VBA to make sure that each one is the end of the month. For some reason, the Eomonth command is not working in the old worksheet, although the same code works fine when I've tested it by entering the data by hand in a test worksheet. This has me thinking that the problem may be the data itself, not the code. Basically, the code is like this: For each cell in Selection Next cel "Rick Rothstein" wrote: Is this a VB question by any chance? And if so, did you want to physically change the date in A1 from whatever it is to the end of its month (thus losing whatever was in the cell to begin with)? If the answer to both of these questions are "Yes", then try this macro out... Sub ChangeDateToEndOfMonth() With Range("A1") .Value = DateSerial(Year(.Value), Month(.Value) + 1, 0) End With End Sub -- Rick (MVP - Excel) "Stratuser" wrote in message ... If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
It's a VBA question. I'm trying to make sure all the dates in a selected
range of imported data are the end of their respective months. The code I have works fine in a test worksheet when I enter the data manually, but not in an old worksheet. This makes me wonder whether I have a data problem. Maybe Eomonth is not evaluating the dates in the old worksheet correctly. It should be as simple as this: For each cel in Selection cel.value = Eomonth(cel.value, 0) Next cel But when my code sees 4/30/2006 in the old worksheet, Eomonth returns 31. "Rick Rothstein" wrote: Is this a VB question by any chance? And if so, did you want to physically change the date in A1 from whatever it is to the end of its month (thus losing whatever was in the cell to begin with)? If the answer to both of these questions are "Yes", then try this macro out... Sub ChangeDateToEndOfMonth() With Range("A1") .Value = DateSerial(Year(.Value), Month(.Value) + 1, 0) End With End Sub -- Rick (MVP - Excel) "Stratuser" wrote in message ... If cell "a1" has a date, how could I check whether the date is the end of the month and if not change it to the end of the month? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
It should be as simple as this:
For each cel in Selection cel.value = Eomonth(cel.value, 0) Next cel Try it this way... For Each Cel In Selection Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0) Next -- Rick (MVP - Excel) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date to End of Month
That worked. Thanks very much.
"Rick Rothstein" wrote: It should be as simple as this: For each cel in Selection cel.value = Eomonth(cel.value, 0) Next cel Try it this way... For Each Cel In Selection Cel.Value = DateSerial(Year(Cel.Value), Month(Cel.Value) + 1, 0) Next -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date in Text to month and Year | Excel Worksheet Functions | |||
convert date to text month | Excel Discussion (Misc queries) | |||
Convert Month, Week, Year to date | Excel Worksheet Functions | |||
convert date to name of month | Excel Discussion (Misc queries) | |||
Convert date to number representing Month | Excel Discussion (Misc queries) |