Change Dates

[jereece]

Is there a way to write a macro to change all dates in a column to the last day of the month IF the date in the cell is currently after the last day of the month and not change it if it's before.

For example, I have a column of dates below

1/15/05
1/25/05
2/1/05
2/10/05

I want my macro to change the last two to 1/31/05 and leave the first two as is.

Thanks for your help.

Jim

 

[Harvey]

assuming the date you want to convert is in A1:

=IF(A1>DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1,DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1,A1)

put this in another cell in the same row, then drag it down your list.
it will give the date, and if the date is newer than last day of this month, it gives the last day of this month.

 

[jereece]

Great. One other question if I may. I realized I also need the reverse. I have a row of start dates. I want to change it to show the the start date of the first day of the month if the start date is before the first or the real date if it's after the first of the month. For example, I have a column of dates below

1/15/05
1/25/05
2/05/05
2/10/05

If this was on my February tab, I would want to change the first two dates to 2/1/05 and leave the last two as is.

I really appreciate the help.

Jim

 

[jereece]

Harvey,

I also just realized something. The code you provided changes my results each month. I have a tab for each month and will sum the results on a separate tab, so I only want the results for that month. So I want to use real dates. I took a stab at this but it does not work. Here's an example.

Lets say in cell F2 I have a date and in cell K2 I want to run the If statement to keep the date the same if it's less than the end of the month. If it's greater than the end of the month, I want the last day of the month to show. Here's what I came up with.

Assume cell F2 has a date of 1/15/2005. Then in cell K2 I have the following:

=IF(F2>1/30/2005,1/31/2005,F2)

The results show 1/1/1900 which is obviously not correct.

What am I doing wrong?

Thanks,
Jim

 

[GorD]

Could you explain again what you are trying to do with a few examples

I would use

=EOMONTH(A1,0) to give the last day of the month contained in A1
and
=EOMONTH(A1,-1)+1 to find the first day.

The EOMonth function needs the analysis tool pack turned on

 

[jereece]

Thanks GorD. Let me explain a little further what I am trying to do.

I have a spreadsheet with tabs for each month and a rollup tab. On each monthly tab, I have the following colums.

A = NAME
B = EMPLID
C = SECURITY_BADGE
D = COMPANY_NME
E = REPORTDATE
F = RELEASEDATE
G = OUTAGE
H = CONTRACT

In column E (REPORT DATE), is obviously the date the contractor starts work. This date stays on the report until the contractor leaves our site which may be a few days to the end of the year.

In column F (RELEASE DATE) is the date they leave our site. This info rolls off the following month.

In column H will be how many hours that person works per week.

What I want to do is calculate how many hours each contractor worked each month. The problem is, the report I get for say February will have some folks on it that started in January and may not be released until the end of the year. So to get a monthly number, I am having to convert the the Release Date on that months worksheet to the last day of that month if the current date is after the last day of the month. Rather than doing this manually, I was wanting a way to automate this.

Likewise for the REPORT DATE, in February the report will show some folks reporting 1/1/2005, but since I want a monthly report, I need to convert any REPORT DATEs that are before the first of the month to the first date of that month. REPORT DATES that fall within that month would stay as is. So if a report date for February was 2/10/05, that would remain where as a report date of 1/10/05 would be changed to 2/1/05 on the February tab.

I hope I am clear. Here is a link to my spreadsheet: http://www.jreece.com/2005VendorHours.zip

Thanks for any help.

Jim

 

[Harvey]

so basicly, you want the dates to be converted to the first day of that month.

=DATE(YEAR(F1),MONTH(F1), 1)

the function you tried will not work because when you make comparisions with dates excel changes them to an integer number of days and every result you will get will be a number.