Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop using Month() and Year()
I have a list of jobs with a report date (column V), part delivery date (column X) and job start date (column Y). I'm trying to write a macro that will output the average queue time (start date - part delivery date) for each report month. This macro must only operate on the jobs that have been "closed", according the the value in column B. The data starts at row 5 and finishes at row 26. Ideally, I'd like columns AD, AE and AF to contain 36 entries for year, month and average queue time. This is what I have so far, but the average does not reset when the month changes, and it won't output each year 12 times to correspond with the month. Any help would be greratly appreciated. ash Sub Report_Queue() Dim Report, ReportDate, ReportMonth, ReportYear Dim dbaseRow Dim QueueMonth, QueueYear Dim QueueDays, QueueCount, AvgQueueDays QueueCount = 0 dbaseRow = 5 Do ReportDate = Sheets("database").Range("v" & dbaseRow).Value ReportMonth = Month(ReportDate) ReportYear = Year(ReportDate) If Sheets("database").Range("b" & dbaseRow).Value = "Closed" Then For QueueYear = 2005 To 2007 For QueueMonth = 1 To 12 If ReportMonth = QueueMonth Then QueueCount = QueueCount + 1 QueueDays = QueueDays + Sheets("database").Range("Y" & dbaseRow).Value _ - Sheets("database").Range("X" & dbaseRow).Value AvgQueueDays = QueueDays / QueueCount Sheets("database").Range("ad" & ((QueueYear - 2004) + 5)).Value = QueueYear Sheets("database").Range("ae" & (QueueMonth + 5)).Value = QueueMonth Sheets("database").Range("af" & (QueueMonth + 5)).Value = AvgQueueDays End If Next QueueMonth Next QueueYear End If dbaseRow = dbaseRow + 1 Loop Until dbaseRow = 26 End Sub -- AMansell ------------------------------------------------------------------------ AMansell's Profile: http://www.excelforum.com/member.php...o&userid=32205 View this thread: http://www.excelforum.com/showthread...hreadid=519567 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
Formula to return last day of month for each month in year? | Excel Programming |