Project 2016 - Create a 2 weeks on, 2 weeks off shift pattern

John, just reassuring you that someone takes notice of your stuff.

I ran that macro (after a few tweaks such as the mm/dd/yy date format and making the 7 days calendar).

It works. Neat.

Trevor,

Man you're killing me here. You really expect me to remember what and why I did when I wrote the macro? I have trouble remembering what I had for lunch yesterday much less what I wrote 7 years ago. I felt lucky to even find that macro on my PC.

But wait, I include comments in my macros just for this purpose, so, the answer to your question is: The DateAdd statement is a VBA function, not a Project VBA Method. The syntax is:

DateAdd(interval, number, date)

The interval is hours so the number must be the number of hours (in terms of 24 hours per day). And since the date will by default start at 8:00, to get a finish at 17:00 fourteen days later, the number argument needs to be 321 (13x24+9).

So, make adjustments to the "seed" for whatever sequence is desired.

John

John,

suppose the cycle is 11 days on and 3 days off.

Which parameters to edit? What's the "321"?

Total days of one cycle is 11+3 = 14.

Trevor,

Thanks for the kudos. I just copied what I had in the cache of macros I've written over the years. I'm glad the tweaks were easy.

It's always a letdown when we spend time researching/composing/answering a user post but then never hear from them again. I attribute some of that to new users who don't yet have the concept of feedback on volunteer forums but I've also seen (or ore appropriately not seen) it from users who have posted multiple times. That's why I always respond back when someone gives feedback.

I appreciate it.

John

ItsSte,

There are a couple of ways to set up a rotating calendar, manually editing work periods for a custom calendar, or with a VBA macro. As it turns out, this type of request has been asked before and I wrote a macro to do it. As written the macro is set up for 14 day cycles of work and non-work but it is easily modifiable to produce the 15 pattern your want.

John

Option Explicit

Option Compare Text

Public ver As String, ResData As String, ResNam As String

Public i As Integer, j As Integer, ResID As Integer, cday As Integer

Public Seed As Variant, Seed2 As Date, Seed3 As Date, S1 As Date, S2 As Date, F1 As Date, F2 As Date

Public fd As Date, StEx As Date, FiEx As Date

Public ResCal As Calendar

Sub Dayson_Daysoff_calendar()

'This macro will create a 14 days on/14 days off sequence

'It is based on a copy of an existing 7 day workweek calendar with default work time (8am to 5pm with 1 hour lunch)

'The number of exceptions is arbitrarily set at 25. That will cover just short of 2 years

'Macro written by John-Project June 24, 2015

'first create the new custom calendar

On Error Resume Next    'in case the custom calendar already exists

BaseCalendarCreate Name:="14on-14off", fromname:="7-day week"

On Error GoTo 0

'Ask user for the date to start the 14on/14off sequence

'   if no date is entered assume current date

Seed = InputBox("Enter sequence start date as mm/dd/yy")

If Seed = "" Then Seed = ActiveProject.CurrentDate

'add 13 days + 9 hours to arrive at a finish of 5:00 pm

fd = DateAdd("h", 321, Seed) 'add 14 days to the start date

For i = 1 To 25

    ActiveProject.BaseCalendars("14on-14off").Exceptions.Add Type:=1, Start:=Seed, Finish:=fd, Name:="exception " & i

    Seed = DateAdd("d", 28, Seed)   'sets start of next group 28 days later at 8:00 am

    fd = DateAdd("h", 321, Seed)    'sets finish of next group 13 days later at 5:00 pm

Next i

End Sub