VBA Schedule help!

This may or may not need VBA. It potentially could be solved using the offset command and/or an array formula. We need to see how you have things organized to judge better.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I was wrong, it did not take the offset command nor an array formula. This can be done with MOD.

I had a couple intermediate formulas and I substituted back until I had the formula in terms of the two helper columns I inserted. I could substitute further back, but that would make the final formula even more complicated.

The two helper columns are Columns D & E and they have the following formulas: =1*LEFT(C2,1) and =1*RIGHT(C2,1) respectively. The reason for multiplying by 1 is to force the string into a numerical value.

Then the magical formula is: =IF(MATCH($B2,$1:$1,0)>COLUMN(),"",IF(MOD(COLUMN()-MATCH($B2,$1:$1,0),$D2+$E2)<$D2,"On Duty",""))

The first part is MATCH($B2,$1:$1,0)>COLUMN() if this is true it means the column is before the start date so display a blank cell.

This part gets the column number for the start date: MATCH($B2,$1:$1,0)

So COLUMN()-MATCH($B2,$1:$1,0) "baselines" the start date column to zero, subsequent columns equate to 1, 2, ...

MOD(COLUMN()-MATCH($B2,$1:$1,0),$D2+$E2)<$D2 works the magic.

Let's take a 2/1 - two on, one off as an example. And furthermore let's say the person starts on June 2.

So Column H, June 2 is 0, June 3 is 1, June 4 is 2, June 5 is 3, June 6 is 4.


June 2 is MOD (0, 2+1) or 0
June 3 is MOD (1, 2+1) or 1
June 4 is MOD (2, 2+1) or 2
June 5 is MOD (3, 2+1) which brings us back to zero.
June 6 is MOD (4, 2+1) or 1

Now we compare this to the ON days or 2. If the MOD yields less than 2 then the person is ON DUTY. Otherwise display a blank cell.

P.S. I put the formulas on the Employee Before sheet. I tested out several combinations like 5/5, 1/2, 2/1 and 3/2 - it seems to work across the board.

That would be a lot more complicated. It probably could be done with formulas, but might have crossed the line to where a VB solution would be easier. I'll look at it if I have time.