Excel week commencing formula

Posted on 2008-10-21
Last Modified: 2011-10-19
Is there a formula that when a name is entered into cell A2 the week commencing date automates in to cell B2? (Week commencing from a monday)
Question by:Carlandrewlewis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22765650
Something like the following snippet?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
    Set rng = Intersect(Target, Me.Range("a:A"))
    If rng Is Nothing Then Exit Sub
    For Each cel In rng
        If cel <> "" Then cel.Offset(0, 1) = DateAdd("d", -(Weekday(Date, vbTuesday)), Date)
End Sub

Open in new window

LVL 59

Expert Comment

by:Chris Bottomley
ID: 22765653
To Create a macro:

Alt + F11 to open the macro editor
  For workbook event handlers:
     In the project tree select as appropriate:
      EXCEL      : thisworkbook
     In the workpane select as appropriate:
      EXCEL      : WorkBook
     In the workpane select the required 'event', (i.e. 'Change').
     Insert the required macro(s) into the selected subroutine.
Close the Visual Basic Editor.

Check Security as appropriate:

In the application select Tools | Macro | Security
Select Medium
Select OK


Author Comment

ID: 22765733
Is there a way to add this into this file. Basically when a date is entered into column A of 'New Seet' it should then automatically fill out column B with the week commencing that will then fill out column C with the week number. I'm just having trouble inserting the code in the right place....
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 59

Accepted Solution

Chris Bottomley earned 500 total points
ID: 22765831
I have posted a change to your change event handler as follows in teh snippet below:

rCell.Offset(0, 1) = DateAdd("d", -(Weekday(rCell, vbTuesday)), rCell)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo e:
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rCell As Range
Set rCell = Intersect(Target, Columns("A"))
If rCell.Value = "" Then Exit Sub
rCell.Offset(0, 1) = DateAdd("d", -(Weekday(rCell, vbTuesday)), rCell)
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = rCell
ActiveSheet.Range("i3").Value = rCell
On Error GoTo e:
Application.ScreenUpdating = True
Exit Sub
Application.ScreenUpdating = True
End Sub

Open in new window


Author Closing Comment

ID: 31508189
Works perfectly, thanks chris!!
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22766160
You're welcome and thanks for the grade.


Featured Post

Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question