Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate a Projected Finish Date based on Work Week and Holidays
All,
I have found the xNetWorkDays code on Google Groups from Myrna Larson and it works great to calculate the number of work days between two dates inluding a list of holidays and whether the work week is 5 , 6, or 7 days. What I'm trying to do is find the projected finish date for a given task based on the start date, the duration allocated for the task, workweek type (Sunday off, Sat/Sun off, or no days off) and a holiday list. I've been using other formulas but can't seem to get it quite right. Any help would be greatly appreciated. What I would be looking for is a function with the following arguments: = EndDate(StartDate, DaysOff, Duration,Holidays) whe StartDate = Start of the task DaysOff is the work week type (5 for Sat/Sun off, 6 for only Sun off, and 7 for no days off) Duration = Number of days allocated for the task Holidays = a range array of holidays that are not worked Mojado44 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate a Projected Finish Date based on Work Week and Holidays
On 15 Nov 2005 13:19:18 -0800, "mojado44" wrote:
All, I have found the xNetWorkDays code on Google Groups from Myrna Larson and it works great to calculate the number of work days between two dates inluding a list of holidays and whether the work week is 5 , 6, or 7 days. What I'm trying to do is find the projected finish date for a given task based on the start date, the duration allocated for the task, workweek type (Sunday off, Sat/Sun off, or no days off) and a holiday list. I've been using other formulas but can't seem to get it quite right. Any help would be greatly appreciated. What I would be looking for is a function with the following arguments: = EndDate(StartDate, DaysOff, Duration,Holidays) whe StartDate = Start of the task DaysOff is the work week type (5 for Sat/Sun off, 6 for only Sun off, and 7 for no days off) Duration = Number of days allocated for the task Holidays = a range array of holidays that are not worked Mojado44 You should be able to adapt the WrkDay function below to your needs. Note that the WrkDay function requires the NWrkDays function also, although you don't need to use it in your worksheet unless you want to. To get an end date: Start Date: A1 Days Off Sun only Duration: A2 Holidays: E1:E10 You would enter: =wrkday(A1,A2,E1:E10,1) For no days off: =wrkday(A1,A2,E1:E10) For Sat & Sun off =wrkday(A1,A2,E1:E10,1,7) (or you could use the ATP function) ============================ Function NWrkDays(StartDate As Date, EndDate As Date, _ Optional Holidays As Range = Nothing, _ Optional WeekendDay_1 As Integer = 0, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0, _ Optional WeekendDay_4 As Integer = 0) As Long ' Sunday = 1; Monday = 2; ... Saturday = 7 'credits to Myrna Dim i As Long Dim Count As Long Dim H As Variant Dim w As Long Dim SD As Date, ED As Date Dim DoHolidays As Boolean Dim NegCount As Boolean DoHolidays = Not (Holidays Is Nothing) SD = StartDate: ED = EndDate If SD ED Then SD = EndDate: ED = StartDate NegCount = True End If w = Weekday(SD - 1) For i = SD To ED Count = Count + 1 w = (w Mod 7) + 1 Select Case w Case WeekendDay_1, WeekendDay_2, WeekendDay_3, WeekendDay_4 Count = Count - 1 Case Else If DoHolidays Then If IsNumeric(Application.Match(i, Holidays, 0)) Then _ Count = Count - 1 End If End Select Next i If NegCount = True Then Count = -Count NWrkDays = Count End Function Function WrkDay(StartDate As Date, ByVal NumDays As Long, _ Optional Holidays As Range = Nothing, _ Optional WeekendDay_1 As Integer = 0, _ Optional WeekendDay_2 As Integer = 0, _ Optional WeekendDay_3 As Integer = 0, _ Optional WeekendDay_4 As Integer = 0) As Date ' Sunday = 1; Monday = 2; ... Saturday = 7 Dim i As Long Dim TempDate As Date Dim Stp As Integer Dim NonWrkDays As Long Dim Temp As Long, SD As Date, ED As Date Stp = Sgn(NumDays) 'Add NumDays TempDate = StartDate + NumDays 'Add Non-Workdays Do While Abs(NumDays) < Temp SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate) ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate) Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3, WeekendDay_4) TempDate = TempDate + NumDays - Stp * (Temp) Loop WrkDay = TempDate End Function ===================================== --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate a Projected Finish Date based on Work Week and Holidays
This works great! Thanx!
mojado44 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Calculate a Projected Finish Date based on Work Week and Holidays
On 15 Nov 2005 16:06:23 -0800, "mojado44" wrote:
This works great! Thanx! mojado44 You're very welcome. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Future date with six day week including holidays | Excel Worksheet Functions | |||
how to calculate a projected date | Excel Worksheet Functions | |||
Calculate End Date (excluding weekends and holidays) | Excel Worksheet Functions | |||
Calculates Dates Based on 7 day week excluding Holidays | Excel Discussion (Misc queries) | |||
Tie a Calendar week to a Scoped Projected Week | Excel Worksheet Functions |