automate dates

[Gary]

Dear Excel Expert,



I have a workbook with 27 tabs titled "Timesheet".



The first tab is a title page that remains the same over the years with the
exception of the "Year" being changed each January.



The remaining 26 tabs contain names of employees with the number of hours
for a day's work. In each tab there are 3 governing dates in 3 different
cells including (1) end of 1st week date (2) end of 2nd week date (3) pay
date. These 3 dates represent a work period that consist of two weeks with
each week beginning on a Monday and finishing on the following Sunday, and a
pay date that is always on a Monday that is 8 days from the end Sunday of
the second week.



An example would be:

For year 2007 a tab is labeled "Week 1-2" that has a (1) end of 1st week
date = 1/07/2007 (2) end of 2nd week date = 1/14/2007 (3) pay date =
1/22/2007.

The next tab is labeled "Week 3-4" that has a (1) end of 1st week date =
1/21/2007 (2) end of 2nd week date = 1/28/2007 (3) pay date = 2/05/2007.

This continues through tab "Week 51-52".

Year 2008 and beyond is identical with the exception of the dates being
different.



I there a way to automate the cells (1) end of 1st week date (2) end of 2nd
week date (3) pay date, on each tab of each year by simply changing the year
on the title page?



Regards,

Gary

 

[Ian]

Two issues here.
1. Find the first Monday in the year, and
2. Calculate dates based on that start date

Issue 1
This code finds the first Monday in the year. It assumes the year (eg 2007)
is in A1 on your first sheet (name unknown, so I used the default Sheet1).
It then puts this date in A1 on Week 1-2.

Issue 2
I've assumed the 3 dates are in A1, A2 and A3 on each "week" sheet.
Week 1-2 A1 is already the correct date (from the code)
In Week 3-4 A1 put ='Week 1-2'!A1+14
In Week 5-6 A1 put ='Week 3-4'!A1+14
etc

In A2 of every sheet put =A1+7
In A3 of every sheet put =A2+8

 

[Gary]

Ian,

I understand the formulas associated with Issue 2 but I do not understand
how to apply or what the code is in Issue 1. Could you help me just a bit
more with that portion of your answer?

Thank you for your time and help,
Gary

 

[Ian]

Sorry, Gary.
1. I wasn't aware you don't know how to use code, amd
2. I forgot to post the code anyway!!!!

The code is below. Some of the lines will have wrapped, particularly the two
lines with comments.

Private Sub Worksheet_Change(ByVal Target As Range)
yearval = Worksheets("Sheet1").Range("A1").Value 'Change Sheet1 to match
your first sheet name. Also change A1 to suit match the cell reference of
the year
dayval = 1
dateval = dayval & "/01/" & yearval
Do While Weekday(dateval) <> 1 ' ie Sunday
dayval = dayval + 1
dateval = yearval & "/01/" & dayval
Loop
Worksheets("Week 1-2").Range("A1") = DateValue(dateval) 'Change A1 to
suit the cell reference of your end of 1st week
End Sub

On your first sheet (I've called it Sheet1), right-click on the tab, then
click View code. In the window with the cursor, paste the code. This will
run any time a change is made to Sheet1.

Note: There is no error checking in this code, so if an invalid year value
is found in the reference cell on Sheet1, you will get an error.

Sorry for the missed code earlier.

 

[Gary]

Ian,

This will work ideal.

Thank you for your time and expertise.

Regards,
Gary

 

[Ian]

Glad it wored. Thanks for the feedback.