Jeffrey

Spongebob wrote:
[snip]

What I am trying to do now is to help fill in the date field. I have
already created a single pull-down with validation which will only
allow for a Monday date to be used via a list of Monday dates I
entered into a 'data' sheet. If any of you know of a better way to
handle this, which will eliminate the need for me to update this list
every year, please let me know. I don't plan to work here forever and
I don't want to leave them with a template which will eventually
become obsolete if it is not maintained.

Your list of Mondays should be formulas that update when the year changes.
Test to see if the date is greater than Jan 10th. If it is, use the current
year in your Mondays formula, if not, use the previous year. See Chip's
site for formula to get Mondays

http://www.cpearson.com/excel/datetime.htm



But the real meat-and-potatoes of my question is - is there a way to
set a date cell in the one-and-only time that the template is called
upon to start a new timesheet (bi-weekly)? I have set auto-date
fields before only to be disappointed when it updates every single
time the file was accessed. Ideally it should insert the closest
Monday's date and allow the user to change it (in case the timesheet
is being done before or after it's given time) via the validation
drop-down I explained above.

You can't do it with a formula, only a macro. You could use a macro like

Private Sub workbook_open()

With Sheet1.Range("A1")
If IsEmpty(.Value) Then
.Value = Date - (Weekday(Date, vbSunday) - 2)
End If
End With

End Sub

Although that won't give you the closest Monday.


I also plan to do a similar task which the template should auto-fill
in a user's name according to what the current user's login is.


See he
http://www.dicks-blog.com/archives/2...ername-in-vba/


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com