|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 11th, 2008, 11:07 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Correction
I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not. I am in need of a formula that will check the month and correct to the last calendar day, i.e. November would be 30 and December would be 31. I need to retain the original month and year and correct only the day. Thanks… -- JPS 
|
|
#2
July 11th, 2008, 11:16 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Correction
=DATE(YEAR(A1),MONTH(A1)+1,0)
and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JPS" wrote in message ... I have a spreadsheet with 1000+ rows and I need to correct a date column, This column should contain the last day of the month, but often does not. I am in need of a formula that will check the month and correct to the last calendar day, i.e. November would be 30 and December would be 31. I need to retain the original month and year and correct only the day. Thanks. -- JPS
|
|
#3
July 11th, 2008, 11:22 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Correction
2 methods
=EOMONTH(A1,0) Requires the analysis toolpak Addin or =DATE(YEAR(A1),MONTH(A1)+1,0) Mike "JPS" wrote: I have a spreadsheet with 1000+ rows and I need to correct a date column, This column should contain the last day of the month, but often does not. I am in need of a formula that will check the month and correct to the last calendar day, i.e. November would be 30 and December would be 31. I need to retain the original month and year and correct only the day. Thanks… -- JPS
|
|
#4
July 11th, 2008, 11:23 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Correction
If your current date is in A1 then use this formula with the relevant cell
formatting to show the date format you require. (If EOMONTH worksheet function is not available then select Tools - Add-Ins and check Analysis ToolPak) =EOMONTH(A1,0) "JPS" wrote: I have a spreadsheet with 1000+ rows and I need to correct a date column, This column should contain the last day of the month, but often does not. I am in need of a formula that will check the month and correct to the last calendar day, i.e. November would be 30 and December would be 31. I need to retain the original month and year and correct only the day. Thanks… -- JPS
|
|
#5
July 11th, 2008, 11:34 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Correction
Bob,
U DA MAN, Thanks.... -- JPS "Bob Phillips" wrote: =DATE(YEAR(A1),MONTH(A1)+1,0) and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JPS" wrote in message ... I have a spreadsheet with 1000+ rows and I need to correct a date column, This column should contain the last day of the month, but often does not. I am in need of a formula that will check the month and correct to the last calendar day, i.e. November would be 30 and December would be 31. I need to retain the original month and year and correct only the day. Thanks. -- JPS
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
