Working Days of Week and DUE DATE for PAYMENT

[XKruodo]

Hi,

A class has 2 batches for students. Monday, Wednesday, Friday and Tuesday,
Thursday, Saturday. Sunday a holiday.

Students can join in between months. One month is considered as 12 WORKING
SESSIONS.

Column A has joining date of students.

4/2/10
4/3/10

Counting from the starting date, i need a date 12 sessions from the starting
date in column B. 4/2/10 ( Friday ) is joining. So 12 sessions would be on
date 4/28/10. I need this in B2 next to A2.

Column H has list of other holidays for which extra sessions have to be
given. Suppose in the above example, H2 contains 26th of April, (MONDAY) as a
holiday, B2 should display 4/30/10 (add more sessions and give 4/30/10
instead of 4/28/10). If H3 contains 10th MAY, Nothing should change as 12
sessions already get over on 30th April.

Note that, B3 will still display 29th April, (12 sessions from 3rd April.
Not adding extra session here as 26th April is not TUE THURS or SAT )

TIA

 

[Niek Otten]

Use Chip Pearson's "better workday" function:

http://www.cpearson.com/excel/BetterWorkday.aspx

 

[Ron Rosenfeld]

Hi,

A class has 2 batches for students. Monday, Wednesday, Friday and Tuesday,
Thursday, Saturday. Sunday a holiday.

Students can join in between months. One month is considered as 12 WORKING
SESSIONS.

Column A has joining date of students.

4/2/10
4/3/10

Counting from the starting date, i need a date 12 sessions from the starting
date in column B. 4/2/10 ( Friday ) is joining. So 12 sessions would be on
date 4/28/10. I need this in B2 next to A2.

Column H has list of other holidays for which extra sessions have to be
given. Suppose in the above example, H2 contains 26th of April, (MONDAY) as a
holiday, B2 should display 4/30/10 (add more sessions and give 4/30/10
instead of 4/28/10). If H3 contains 10th MAY, Nothing should change as 12
sessions already get over on 30th April.

Note that, B3 will still display 29th April, (12 sessions from 3rd April.
Not adding extra session here as 26th April is not TUE THURS or SAT )

TIA

I've implemented a UDF that allows one to specify which days of the week are
Weekend days. This could be used in your application.

Note that because it works similar to the WORKDAY worksheet function, you need
to specify one less than the number of days you want in order to return the
date of the last session.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF)

B2:
=IF(OR(WEEKDAY(A2)={2,4,6}),WrkDay(A2,11,H1:H10,1,3,5,7),
IF(OR(WEEKDAY(A2)={3,5,7}),WrkDay(A2,11,H1:H10,1,2,4,6)))

==============================================
Option Explicit

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, _
Optional weekendday_5 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, weekendday_5
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, _
Optional weekendday_5 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, weekendday_5)
TempDate = TempDate + NumDays - Stp * (temp)
Loop

WrkDay = TempDate
End Function
=============================================
--ron

 

[XKruodo]

I am ok with functions. But all this VBA and Macros are confusing. Let me see
if i can make this work. I knew doing this using normal functions would be
tough..or say not convenient..

Thanks for the help.

 

[Ron Rosenfeld]

I am ok with functions. But all this VBA and Macros are confusing. Let me see
if i can make this work. I knew doing this using normal functions would be
tough..or say not convenient..

Thanks for the help.

Good.

Let me know if you run into any problems.
--ron