Change Dates

jereece

New Member
Joined
Mar 18, 2002
Messages
25
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
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

New Member
Joined
Mar 18, 2002
Messages
25
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

New Member
Joined
Mar 18, 2002
Messages
25
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

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

New Member
Joined
Mar 18, 2002
Messages
25
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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.
 

Forum statistics

Threads
1,148,228
Messages
5,745,482
Members
423,953
Latest member
MrC54

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top