I am trying to make my template extremely user friendly. Every year I add
one or two upgrades to my companies timesheet template. This year I am going
all out and making it nearly effortless to create and fill in. The idea is
that if the redundant, brainless information is filled in for you then you
can spend more time writing descriptions for every time entry.

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.

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.

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.

I hope I didn't make my question more complicated than necessary.

TIA
Jeffrey K. Ries

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