Auto Column Date

[ExcelRoy]

Hi everyone,

I have the following code that adjusts the previous week and next 2 weeks unhidden when the workbook is opened
The dates are displayed in Row 3 (In columns from column Q:KV)

What i would like is for the display of weekends (Currently only displays weekdays)


Private Sub Workbook_Open()
Dim i As Long
With Worksheets("Live Service Orders")
.Cells.EntireColumn.Hidden = False
For i = 17 To .Cells(3, Columns.Count).End(xlToLeft).Column
If .Cells(3, i).Value < Date - 14 Or .Cells(3, i) > Date + 42 Then
.Columns(i).Hidden = True
ElseIf Weekday(.Cells(3, i), 2) > 5 Then
.Columns(i).Hidden = True
End If
Next
End With
End Sub

Many thanks for any help

 

[Joe4]

These are the lines that hide the weekend dates (checking for weekday number greater than 5):

ElseIf Weekday(.Cells(3, i), 2) > 5 Then
.Columns(i).Hidden = True

Simply remove those lines (or comment them out), and it will stop hiding the weekend days.

 

[ExcelRoy]

Many thanks Joe4

 

[ExcelRoy]

Hi Joe4,

Could this be changed to only show the previous week, and following 2 weeks (Including weekends) instead of days?

Thanks

 

[Joe4]

Could this be changed to only show the previous week, and following 2 weeks (Including weekends) instead of days?

Sure. It is pretty easy, when you see what the code is doing, specifically this line:

If .Cells(3, i).Value < Date - 14 Or .Cells(3, i) > Date + 42 Then

Notice the -14 (subtract 2 weeks) and +42 (add 6 weeks).
Just change those to -7 and +14, respectively, to do what you want.

 

[ExcelRoy]

Hi Joe4,

Thanks, is it possible for "complete" weeks to be shown and not, lets say from Wednesday as an example but show fromMonday etc

Thanks

 

[Joe4]

Thanks, is it possible for "complete" weeks to be shown and not, lets say from Wednesday as an example but show fromMonday etc

That is a bit more complex, and is really a new question (as the original question in this thread was really about including weekends).
It would be best to post that in its own new thread.