Create schedules in Excel with shift according to pay periods

Let’s take the frustration out of user forms

Become an Affiliate and earn 25% on Course Sales

For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting

I am able to provide online help on your computer at a reasonable rate.

I use a Blue condenser Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.
code
Sub rotate()
Dim r, c As Long
Dim typsh As String
Dim mon As Long
r = 7
c = 2
mon = 1

Sheet1.Activate
Do While r (less than) 21
‘—Morning start———-
If r = 7 Or r = 8 Or r = 9 Or r = 10 Or r = 11 Then
mon = 1
cnt = 0
c = 2
typsh = “M”
Call firstshift(mon, r, c, typsh)
typsh = “A”
Call secondshift(mon, r, c, typsh)
typsh = “N”
Call firstshift(mon, r, c, typsh)
typsh = “M”
Call secondshift(mon, r, c, typsh)
typsh = “A”
Call firstshift(mon, r, c, typsh)
typsh = “N”
Call secondshift(mon, r, c, typsh)

End If

If r = 12 Or r = 13 Or r = 14 Or r = 15 Then
mon = 1
cnt = 0
c = 2
typsh = “A”
Call firstshift(mon, r, c, typsh)
typsh = “N”
Call secondshift(mon, r, c, typsh)
typsh = “M”
Call firstshift(mon, r, c, typsh)
typsh = “A”
Call secondshift(mon, r, c, typsh)
typsh = “N”
Call firstshift(mon, r, c, typsh)
typsh = “M”
Call secondshift(mon, r, c, typsh)

End If

r = r + 1
Loop
End Sub

Sub firstshift(mon, r, c, typsh)
Dim startDate, endDate As Date
wdate = Sheet1.Cells(5, c).Value
startDate = DateSerial(2022, mon, 10)
endDate = DateSerial(2022, mon, 26)
Do While wdate (greater than) startDate
wdate = Sheet1.Cells(5, c).Value
If wdate = endDate Then GoTo ende
Sheet1.Cells(r, c).Select
If ActiveCell.Value = “” Then
ActiveCell.Value = typsh
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
c = c + 1
Loop
ende:
End Sub

Sub secondshift(mon, r, c, typsh)
Dim endDate, middate As Date
wdate = Sheet1.Cells(5, c).Value
endDate = DateSerial(2022, mon, 26)
middate = DateSerial(2022, (mon + 1), 1)
Do While wdate (greater than)= endDate
If c(greater than)92 Then GoTo ende

wdate = Sheet1.Cells(5, c).Value
If wdate(less than)= middate Then GoTo nextone
Sheet1.Cells(r, c).Select

If ActiveCell.Value = “” Then
ActiveCell.Value = typsh
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If

c = c + 1
Loop
nextone:
mon = mon + 1
eardate = DateSerial(2022, mon, 1)
startDate = DateSerial(2022, mon, 10)
Do While wdate(greater than)= eardate
wdate = Sheet1.Cells(5, c).Value
If wdate (greater than) startDate Then GoTo ende
Sheet1.Cells(r, c).Select
If ActiveCell.Value = “” Then
ActiveCell.Value = typsh
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If

c = c + 1
Loop
ende:
End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English