insert columns based on the weekday value of a date

sandero1970

New Member
Joined
Aug 23, 2013
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I'm trying to find a way to insert columns based on the weekday value of a date in the field.
All the dates of the month from 1 to 28 or 30 or 31 are located on line 2 as from column 2.
I need to make "a weekly planning" from Monday to Sunday but I have to shift the first day of the month in the correct column
For example 1/apr/2021 is a Thursday. When data is extracted from the source, the date is located in cell B2 and the weekday value (4) is located in cell B1.
Thursday being day 4, I need to insert 3 columns after column 1 so that 1st of April 21 comes in column 5.

Of course the number of inserted columns depends on the weekday of the 1st of the month
if 2 then 1 col
if 3 then 2 col
if 4 then 3 col
...
If 7 then 6 col

After that I will transform this in a Weekly planning from Mo to Su and will copy each block down the previous one so that I have very week each down the previous one.

Thx for your help.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Not sure I understand what you are doing but see if you can work with this.

VBA Code:
Sub tt()
Dim dy As Long
dy = Weekday(Range("B2").Value)
    If dy <> 2 Then
        Range("B2").Resize(, dy - 2).Insert xlShiftToRight
    End If
End Sub

What if the first of the month falls on Sunday?
 
Upvote 0
Hi thanks for the answer
I need to make a table from monday to sunday.
So if in B2 het date correspond to a wednesday, for example, I have to insert 2 columns
If B2 is a thursday, I have to insert 3 columns...
If B2 is a monday, I do not insert columns
 
Upvote 0
This should take care of the Sunday issue.

VBA Code:
Sub tt2()
Dim dy As Long
dy = Weekday(Range("B2").Value)
    If dy = 1 Then
        Range("B2").Resize(, 6).Insert xlShiftToRight
    ElseIf dy <> 2 Then
        Range("B2").Resize(, dy - 2).Insert xlShiftToRight
    End If
End Sub
 
Upvote 0
thx! exactly what I want except I need to insert a column and not shift cell
so I used this and it works fine:
If dy = 1 Then
Columns("B:B").Resize(, 6).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
ElseIf dy <> 2 Then
Columns("B:B").Resize(, dy - 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow

Thank you for support!
 
Upvote 0
That's good. But the proper syntax is either 'Columns("B")' or 'Range("B:B")', or using index 'Columns(2)'.
Anyway, glad it works for you. Thanks for the feedback.
Regards, JLG
 
Upvote 0
thx! exactly what I want except I need to insert a column and not shift cell
so I used this and it works fine:
If dy = 1 Then
Columns(2).Resize(, 6).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
ElseIf dy <> 2 Then
Columns(2).Resize(, dy - 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top