We help IT Professionals succeed at work.
Troubleshooting Question

Fill Holiday column automatically. Approach, methods to use?

84 Views
Last Modified: 2020-09-03
Hi Experts,

See images.

I need to automatically fill in holidays in a timesheet worksheet

Based on my current knowledge, I'd need to search another worksheet for the holiday records. Maybe use a Lookup. If found, switch back to the timesheet and fill that holiday's column and format accordingly. In addition, fill in the 8 hours time at the column's bottom.

I don't know the details yet. At this stage, I just want to know if the approach is good.

I am open to better methods/approaches.

I don't know if it matters or not but... For what it's worth, in the first image, I've explained the formulas used to derive the day numbers and day initials.

The second image shows what I'd like to eventually have.

Comment
Watch Question

Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM
CERTIFIED EXPERT

Commented:
The approach sounds good.
You can use another worksheet, or maybe place the holiday records into an Access database.
Then, when generating the spreadsheet, you can use VBA to pull the records from either the XLS or Access db and format the days.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
You are making this project much harder than it needs to be.

I suggest using Conditional Formatting to color weekend cells blue and holiday cells gray. And you should put actual dates in cells D2:R3, and use number formats that display the dates as either the day or weekday.

Your desire to have "Holiday" down the associated column can also be accomplished but with more difficulty.

Had you posted a sample workbook, I'd have fixed it for you. As an alternative, I'll try to mock up something you can copy.
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Thanks for your responses!

@byundt... The first image was for reference only. It's already done. Nothing more to add.  I wasn't sure if including it would help or not.

I just need help with the holidays part.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Timesheet.xlsx 
Sample file using suggested formulas and conditional formatting. The list of holidays is kept in a named range called Holidays on worksheet Data.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I believe that getting the holiday column to say Holiday is going to require VBA code. A Worksheet_Change event sub could trap a change to cell B2, and then put the Holiday text in the appropriate cells. But it should also clear the values in those same cells if the column is not a holiday. In addition, the user would need to enable macros to get the feature to work.

Are you sure you want that feature (display Holiday as text) given that you'd risk wiping out previously entered data?
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Thanks again for all your help.

Maybe some additional notes / requrements will help.

This need came up because users were forgetting to enter holiday hours on their timesheet.

I store related worksheets and macros on the network to make deployment easier.

For the same reason, I'd like to store the holiday file xls on the network.

I'd like the holiday macro to run after the user enters a project number in column A. Currently, when that's done, a vba lookup is done to enter the title in the adjacent col B. That works fine as it is. I'm guessing getting the holiday macro to run after that point may work.

I think the brunt of that macro would basically search the holiday worksheet for any day on the timesheet that matches a holiday date. If so, it fills the matching timesheet column date accordingly.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I added VBA code to put the text "Holiday" in the appropriate cells. This code is triggered by a change to the first date of the period.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("B2"), Target) Is Nothing Then
    HolidayLabel Me.Range("D2:R2"), ActiveWorkbook.Names("Holidays").RefersToRange, Me.Range("D4:R4")
End If
End Sub

Sub HolidayLabel(rgDates As Range, rgHolidays As Range, rgHours As Range)
Dim cel As Range
Dim i As Long, j As Long
Dim v As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each cel In rgDates
    j = j + 1
    v = Application.Match(cel, rgHolidays, 0)
    For i = 1 To 7
        If IsError(v) Then
            If rgHours.Cells(i, j).Value <> "" Then
                If Not IsNumeric(rgHours.Cells(i, j).Value) Then rgHours.Cells(i, j).ClearContents
            End If
        Else
            rgHours.Cells(i, j).Value = Mid("Holiday", i, 1)
        End If
    Next
Next
Application.EnableEvents = True
End Sub
Timesheet.xlsm
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Hi byundt...

That works a treat! Thank you.

Can you change it so that it searches the holidays in an external xls file instead of searching the Data worksheet? Keeping an external xls file makes maintenance easier.

I see that you use the Holidays array for the conditional format and placing the 8 hr value at the bottom.

Can you revise it to first populate the Holidays array using the values from the external xls file?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
A better approach would be to populate the Holidays named range in the time sheet workbook using values from the external .xls file. This could be done using a Workbook_Open event sub.

Although such a sub could get values from a closed workbook, that process is slow. It would be better if the .xls file were already open, or if it were permissible for the Workbook_Open sub to open that workbook.

If you would like me to provide such a sub, please post the path to the .xls file as well as copies of both the .xls and time sheet files. I want to make sure all the moving parts mesh properly.
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Here's a sample of the timesheet. It does not include your prior conditional formatting. But has it's own conditional formatting.

If you could add in the conditional formatting for the holiday.

No need for the word 'Holiday' anymore because it blocks the way when I work on a holiday and need to show the hours. Instead, like you did earlier, just grey out (grey color) the holiday column and add in the 8 hr value at the column bottom.

If you are strapped for time, please just include the solution for the named range and opening the holiday.xls file and populating the named range. Then, I can probably figure out the conditional formatting based on your prior post.

The holiday.xls file can include the same data you have on your Data tab. I presume the holiday.xls will be closed when I open my timesheet. So, opening the holiday.xls file is fine
Test-timesheet.xls
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I added code to ThisWorkbook code pane. The Workbook_Open sub runs if macros are enabled and you open the workbook. It calls a sub that updates the list of holidays from a master workbook.

As written, the master workbook is called Timesheet.xlsm, and is located in a particular folder on my Macintosh. There is a named range called Holidays in that workbook. You will need to update the code to suit your actual workbook name, its path and name of the range containing the holidays.

The UpdateHolidays sub will update the holiday list in workbook Test-timesheet.xls every time you open the workbook. If it doesn't already exist, the macro will create a worksheet called data and a named range Holidays.

The code does not create the sum formulas nor conditional formatting in worksheet Test-timesheet.xls. It assumes that has already been done. I updated your sample workbook so you can see how to do it. You will note that all cells in columns D:R share the same conditional formatting formula for the gray color. You will also note that I updated the SUM formulas to automatically add 8 hours if that column is a holiday--those 8 hours are in addition to anything the user puts in row 7 PAID TIME OFF.

FWIW, you made your life harder than it needs to be by ignoring my suggestion to put actual dates in cells D2:R3. You also made it harder by having a different conditional formatting in each column in the range D:R. My previously posted workbook showed how you could do it easier.
Test-timesheet.xls

Private Sub Workbook_Open()
UpdateHolidays
End Sub

Sub UpdateHolidays()
Dim wb As Workbook
Dim bAlreadyOpen As Boolean
Dim wsData As Worksheet
Dim nmHolidays As Name
Dim celHome As Range, rgHolidaysMaster As Range, rgHolidays As Range
Dim n As Long

Application.ScreenUpdating = False
On Error Resume Next
With ActiveWorkbook
    Set celHome = ActiveCell
    Set wsData = .Worksheets("Data")
    If wsData Is Nothing Then
        Set wsData = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
        wsData.Name = "Data"
    End If
    wsData.Visible = xlSheetHidden
    
    Set nmHolidays = .Names("Holidays")
    If nmHolidays Is Nothing Then
        Set nmHolidays = .Names.Add("Holidays", .Worksheets("Data").Range("A2"))
    End If
    Set rgHolidays = nmHolidays.RefersToRange
End With

bAlreadyOpen = True
Set wb = Workbooks("Timesheet.xlsm")    'Name of workbook with master list of Holidays
If wb Is Nothing Then
    bAlreadyOpen = False
    Set wb = Workbooks.Open("\\Mac\Home\Desktop\Old HP drive\VBA\Sample '20\Timesheet.xlsm")
End If

Set rgHolidaysMaster = wb.Worksheets("Data").Range("Holidays")
If Not rgHolidaysMaster Is Nothing Then
    n = rgHolidaysMaster.Cells.Count
    rgHolidays.ClearContents
    Set rgHolidays = rgHolidays.Cells(1).Resize(n, 1)
    rgHolidaysMaster.Copy rgHolidays
    nmHolidays.RefersTo = "=Data!" & rgHolidays.Address
    If bAlreadyOpen = False Then wb.Close SaveChanges:=False
End If

Application.Goto celHome
On Error GoTo 0

End Sub


NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Hi byundt...

It doesn't work. I put the Holidays.xlsm in the same folder as the timesheet.xls.

I'll upload the file and code
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
The code here is also in Test-timesheet-EE-v1.xls > ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
UpdateHolidays
End Sub

Sub UpdateHolidays()
Dim wb As Workbook
Dim bAlreadyOpen As Boolean
Dim wsData As Worksheet
Dim nmHolidays As Name
Dim celHome As Range, rgHolidaysMaster As Range, rgHolidays As Range
Dim n As Long

Application.ScreenUpdating = False
On Error Resume Next
With ActiveWorkbook
    Set celHome = ActiveCell
    Set wsData = .Worksheets("Data")
    If wsData Is Nothing Then
        Set wsData = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
        wsData.Name = "Data"
    End If
    wsData.Visible = xlSheetHidden
    
    Set nmHolidays = .Names("Holidays")
    If nmHolidays Is Nothing Then
        Set nmHolidays = .Names.Add("Holidays", .Worksheets("Data").Range("A2"))
    End If
    Set rgHolidays = nmHolidays.RefersToRange
End With

bAlreadyOpen = True
Set wb = Workbooks("Holidays.xlsm")    'Name of workbook with master list of Holidays
If wb Is Nothing Then
    bAlreadyOpen = False
    Set wb = Workbooks.Open("Holidays.xlsm")
End If

Set rgHolidaysMaster = wb.Worksheets("Data").Range("Holidays")
If Not rgHolidaysMaster Is Nothing Then
    n = rgHolidaysMaster.Cells.Count
    rgHolidays.ClearContents
    Set rgHolidays = rgHolidays.Cells(1).Resize(n, 1)
    rgHolidaysMaster.Copy rgHolidays
    nmHolidays.RefersTo = "=Data!" & rgHolidays.Address
    If bAlreadyOpen = False Then wb.Close SaveChanges:=False
End If

Application.GoTo celHome
On Error GoTo 0

End Sub

Open in new window

Test-timesheet-EE-v1.xls
Holidays.xlsm
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I put the Holidays.xlsm in the same folder as the timesheet.xls
I revised the code to take advantage of that fact.
Test-timesheet.xls
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
How do I set a Debug breakpoint if the code is in the unopened xls file?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Holidays.xlsm didn't have a worksheet named Data. I changed the code in the attached workbook to look for a named range Holidays on Sheet1 instead.
Test-timesheet-EE-v1.xls
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Because the Workbook_Open sub is calling sub UpdateHolidays, you can just run UpdateHolidays manually. In so doing, you don't need to set a Debug breakpoint in an unopened file (which is impossible).
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Thanks for the Debug tip. Much appreciated.

Trying to understand your code.

What does this section do?

With ActiveWorkbook
    Set celHome = ActiveCell
    Set wsData = .Worksheets("Data")
    If wsData Is Nothing Then
        Set wsData = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
        wsData.Name = "Data"
    End If
    wsData.Visible = xlSheetHidden
    
    Set nmHolidays = .Names("Holidays")
    If nmHolidays Is Nothing Then
        Set nmHolidays = .Names.Add("Holidays", .Worksheets("Data").Range("A2"))
    End If
    Set rgHolidays = nmHolidays.RefersToRange
End With

Open in new window

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
That block does the following:
  • Sets a return address, so the user is not jarred by ending up on a different worksheet 
  • Tests whether worksheet Data exists. If not, it creates one.
  • Hides worksheet Data
  • Tests whether named range Holidays exists. If not, it creates one
  • Assigns variable rgHolidays to named range Holidays
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
I unhide the Data workshee and delete it. Then save the timesheet.

The next time I open the timesheet, the Data worksheet is created but it doesn't have the data from the holidays from the holidayfile.xlsm

Option Explicit

Private Sub Workbook_Open()
    UpdateHolidays
End Sub

Sub UpdateHolidays()
Dim wb As Workbook
Dim bAlreadyOpen As Boolean
Dim wsData As Worksheet
Dim nmHolidays As Name
Dim celHome As Range, rgHolidaysMaster As Range, rgHolidays As Range
Dim n As Long

Application.ScreenUpdating = False
On Error Resume Next
With ActiveWorkbook
    Set celHome = ActiveCell
    Set wsData = .Worksheets("Data")
    If wsData Is Nothing Then
        Set wsData = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
        wsData.Name = "Data"
    End If
    wsData.Visible = xlSheetHidden
    
    Set nmHolidays = .Names("Holidays")
    If nmHolidays Is Nothing Then
        Set nmHolidays = .Names.Add("Holidays", .Worksheets("Data").Range("A2"))
    End If
    Set rgHolidays = nmHolidays.RefersToRange
End With

bAlreadyOpen = True
Set wb = Workbooks("holidayfile.xlsm")    'Name of workbook with master list of Holidays
If wb Is Nothing Then
    bAlreadyOpen = False
    Set wb = Workbooks.Open(ActiveWorkbook.Path & Application.PathSeparator & "holidayfile.xlsm")
End If

Set rgHolidaysMaster = wb.Worksheets("Data").Range("Holidays")
If Not rgHolidaysMaster Is Nothing Then
    n = rgHolidaysMaster.Cells.Count
    rgHolidays.ClearContents
    Set rgHolidays = rgHolidays.Cells(1).Resize(n, 1)
    rgHolidaysMaster.Copy rgHolidays
    nmHolidays.RefersTo = "=Data!" & rgHolidays.Address
    If bAlreadyOpen = False Then wb.Close SaveChanges:=False
End If

Application.GoTo celHome
On Error GoTo 0

End Sub

Open in new window


What does the Set nmHolidays = .Names("Holidays")line do? Before running this line, the value is Nothing. After running it, the value is the full path of HoldidayFile.xlsm.

How does it get that value?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I unhide the Data workshee and delete it. Then save the timesheet.
Deleting worksheet Data would be a problem. Doing so breaks the formulas that return Holiday hours in row 5. It also breaks the Conditional Formatting if a given day falls on a holiday. Letting the code recreate the Data worksheet won't fix these problems.

If you want to test whether the code is working I suggest clearing the values in worksheet Data. But please don't delete the named range (using the Name Manager) or the worksheet.

In statement 40, the code is looking for a named range Holidays in a worksheet named Data in workbook holidayfile.xlsm. If the workbook hasn't been opened, or it doesn't contain a worksheet named Data, or there is no named range Holidays--the code is not going to work.

nmHolidays is a named range object pointing to the named range Holidays. You use a Set statement when you create objects.


byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Please post the workbooks that aren't working the way you want. I need to see both of them so I can figure out where the problem lies.
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Your vba solution seems to be working.

Closing then restarting Excel fixed it.

The holiday named range is populating as desired. It also updates if I revise the holidays xlsm file.

Now, the issue seems to be the conditional formatting.

I ignored your suggestion earlier because the conditional formatting I had was working before my request. I was using this timesheet for a long time. It served me fine.

I guess adding this new conditional threw a wrench in there.

You say that I should put actual dates in cells D2:R3, and use number formats that display the dates as either the day or weekday.

How would i do that?

Because currently, when I enter the calendar period , e.g. 2/1/20 in cell B2, the days 1 thru 15 populate based on that.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
The very first workbook I posted shows the formulas in rows 2 and 3, as well as the conditional formatting. 
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I changed the attached workbook so you have the same conditional formatting for D4:S16, rather than separate criteria for each column. You will need to delete the weekend conditional formatting you currently have in each column if you want to take this route.

Although I still recommend putting actual dates in D2:S3 and using Custom number format to display the day and weekday, I used the formula $B$2:D$2-1 to calculate the dates on the fly and thereby avoid the need for you to mess with D2:S3.

Test-timesheet-EE-v1.xls
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Hi buyndt!

I'll take a look when I get to the office

I appreciate your patience and detailed help with this.

Mahalo!
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
I downloaded your last Test-timesheet-EE-v1.xls

When I change to a 2nd period, i.e. 1/16/20, 2/16/20, etc, the blue weekends don't color correctly. Also, the holiday doesn't highlight.

The 1st periods are fine, i.e. 1/1/20, 2/1/20, etc.

See Holidays.xlsm

Wrong weekend colors on 1/16/20 period
Wrong weekend colors on 2/16/20 period
What is the cause of that?

All conditions in Test-timesheet-EE-v1.xlsHolidays.xlsm
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Oops!

I changed the conditional formatting formula to calculate the date as $B$2+COLUMNS($D$2:D$2)-1. That works no matter what the value in B2.

Also, note that cells D4:S16 should have only two conditional formats, not the four as shown in your bottom screenshot. Delete Rule the ones that you aren't using (second and fourth).

Test-timesheet-EE-v1.xls
NVITEnd-user support
CERTIFIED EXPERT

Author

Commented:
Thanks, buyndt.

Downloaded and tested.

All 2nd period holidays and related hrs show incorrectly. e.g.
3/16/20: grey condition shades correct 3/27 day but the 8 value is incorrect on 3/19
4/16/20: grey condition shades correct 4/23 day but the 8 value is incorrect on 4/18

Here's a different behavior pattern:
6/16/20 puts a holiday on 6/18 although not listed in holidays.xlsm.
8/16/20 puts a holiday on 8/23 although not listed in holidays.xlsm.

Holidays.xlsm attached.
Holidays.xlsm
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.