Total hours worked on a holiday in a schedule in Excel

Total hours worked on a holiday in a schedule in Excel. Keep track of overtime hours that are worked on a holiday. Link to how to record overtime
Check out my online courses
All my courses include online support and a user manual
Let me teach you the VBA that I have learn in my five years of consulting

Let’s take the frustration out of user forms

Become an Affiliate and earn 25% on Course Sales

For more help visit my website or email me at

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


Follow me on twitter

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

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

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
Cells(r, 33).Value = ot
r = r + 1

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

End Sub

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

Rate this post

Bài viết liên quan

Theo dõi
Thông báo của
1 Comment
Cũ nhất
Mới nhất Được bỏ phiếu nhiều nhất
Phản hồi nội tuyến
Xem tất cả bình luận
surya pratap Singh

Hi Barb, Thank you for sharing all intresting and valuable knowledge with us.I was wondering if you can include a quick video on XLookup also