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