insert columns based on the weekday value of a date

sandero1970

New Member
Joined
Aug 23, 2013
Messages
8
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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?
 

sandero1970

New Member
Joined
Aug 23, 2013
Messages
8
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

sandero1970

New Member
Joined
Aug 23, 2013
Messages
8

ADVERTISEMENT

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!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

sandero1970

New Member
Joined
Aug 23, 2013
Messages
8
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,652
Messages
5,626,093
Members
416,161
Latest member
David1966Lewis

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
Top