Total hours worked on a holiday in a schedule in Excel

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.

Template with code available for $50 USD

code
Sub updateothrs()
Dim r, c As Long
Dim row As Long
Dim ot, hot As Long
Dim myrng As Range
Dim mycell As Range

Sheet3.Activate
r = 5

Do While r (is less than) 17 ’99
ot = Cells(r, 33).Value
ot = 0
‘define my range as the days of the month
Set myrng = Sheet3.Range(Cells(r, 2), Cells(r, 32))

For Each mycell In myrng
‘determine if it is a number
If Application.WorksheetFunction.IsNumber(mycell.Value) = True Then
If mycell.Value (is greater than) 0 Then
ot = ot + mycell.Value
End If
End If
Next
Cells(r, 33).Value = ot
r = r + 1
Loop

End Sub

Sub holiday()
Dim row, c As Long
Dim daterng As Range
Dim dcell As Range
Dim day As Date
Dim hot As Long
row = 4 ‘3
Do While Sheet3.Cells(row, 37) (does not equal) “”
‘select a holiday
day = Sheet3.Cells(row, 37).Value
‘select the range of dates at the top of the sheet
Set daterng = Sheet3.Range(Cells(2, 2), Cells(2, 32))

For Each dcell In daterng.Cells

If dcell.Value = day Then
c = dcell.Column
For row = 5 To 17
Sheet3.Cells(row, c).Select
hot = Cells(row, 34).Value
‘check if the cell contains a number
If Application.WorksheetFunction.IsNumber(Sheet3.Cells(row, c).Value) = True Then
If Sheet3.Cells(row, c).Value(greater than) 0 Then
hot = hot + Sheet3.Cells(row, c).Value
End If
End If
Cells(row, 34).Value = hot
‘subtract the holiday overtime from the regular overtime
Cells(row, 33).Value = Cells(row, 33).Value – hot
Next row

End If
Next dcell
row = row + 1
Loop

End Sub

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