To anyone who reads this, thanks in advance for any input or advice.

I have to fill out a weekly attendance report for my job. I am interested in automating as much of this process as possible. Every week I have to type in the date for every day of the week in question into a MS Word table column (a MS Word spreadsheet). What I would like to do is use a formula so that after entering the first day's date, the rest of the days fill in automatically.

I have been playing with Quick Parts to accomplish this but to no avail. I know there is a function to automatically enter the date whenever the document is opened, and am fine with doing my attendance the first day of every week if necessary to automate this process.

Does anybody know how this can be done?



Thanks again,

Del

Use fields and bookmarks

see below, and let me know if you need more information

http://addbalance.com/word/datefields1.htm

Also, check out the Datecalc document

http://www.gmayor.com/downloads.htm#Third_party

Hi Del,

Do indeed check out my Microsoft Word Date Calculation Tutorial, available at:
http://lounge.windowssecrets.com/ind...owtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the item titled 'Calculate a Date Sequence'. You should also read the document's introductory material.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Cross-posted at: http://answers.microsoft.com/en-us/o...2-152e84046306

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Dear Macropod,

I want to apologize for deviating from appropriate protocol for posting. I should've waited longer to see if there was a response here before posting elsewhere. I will adhere to the practices outlined in the piece you linked to in the future.

I want to thank you for the document. I spent much of my Sunday playing around with some of the formulas. Eventually I did use precisely the section that you referenced to accomplish what I originally set out to do--develop a form that I could use on a particular day of the week to fill out all of the dates for my attendance report.

Of course, no good deed goes unpunished! I showed my boss today and he was thrilled at the potential of using formulas to automate the process for others in the organization. My system of choosing one day and setting up the formulas accordingly was not flexible enough for him, though. He wants me to investigate setting it up so that an individual can simply enter any date of the week and have it output the rest accordingly.

I suspected this might be the case and tried hard over the weekend to figure this out, but got stumped. Would someone mind helping me develop a formula that will find the nearest days in a single week based off of any single day of that week?

For example: some people in our organization do their attendance reports on different days each week, filling it out on Tuesday one week and on Thursday the next. I know that I could develop a specific template for them if they did it on a particular day, but people want flexibility to do it whenever.

It seemed like "Calculate a day, date, month, and year, using n days delay" was the closest related section in the document, but I don't know what to do when I'm not basing all of the other formulas off of the {DATE} field.

Thanks in advance for your help!

Sincerely,

Del

PS I have attached the attendance report we use with the formulas I plugged in. It is set up for me to use on Tuesdays (otherwise the dates will all be off). I use two keystrokes, "Crtl - A" followed by "F9." Works great! Thanks again.

Attached Files

01.08.12 Attendance Report Template (blank).docx (23.1 KB, 45 views)

Hi Del,
Maybe I'm missing something, but I can't see that it matters which days an employee works - they'd simply fill in the attendance sheet accordingly. All you'd need is a prompt/formula to get the appropriate week-ending date. Is that always a Sunday?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Dear Macropod,

I apologize for not being clearer in my post. Employees do indeed fill out their attendance report for every day of the week, regardless of whether they worked a particular day or not. What I meant was that they fill out their attendance report on different days (some, if they're not being good little worker bees, will do them the following week--but I'm not looking to fix that problem).

My personal solution requires me to fill out my attendance report on a particular day (I chose Tuesday). Since I chose Tuesday, all of the formulas reference {DATE} in the Tuesday date box. If I entered the template I devised today (Monday) and pressed F9 to update all of the fields, all of the dates would be off.

The way our form is devised our week actually ends on Saturday. My boss wants to know if there is a way to allow someone to fill out his/her attendance report any day of the week. That is to say, the form would "know" that, if Sally wants to fill out the form on Wednesday, it'll adjust accordingly and all of the fields will display the right dates. Whereas, if I gave Sally my template right now, it would not work because my template requires someone to fill out their attendance report a particular day.

I apologize if I'm still being obtuse.

Thanks again for your help!

Del

Hi Del,

I think that keying the dates to any day other than the week-ending or week-starting date is a mistake. Since those dates will (presumably) be common to all employees, that's what you really should work with.

Apart from needing the week-ending or week-starting date, I wouldn't have thought any of the other dates would be needed; surely people only need to know which day of the week they're filling the form out for, rather than the date of that day as well, given that the whole form only spans one week.

Be that as it may, to make the report work as a form, you probably should use formfields, to constrain where/what the users can input. Have a play with the attached.

Attached Files

Attendance Report Template.docx (24.8 KB, 70 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Dear Paul,

Thank you in advance for your time. This has been a long time in coming and I want to apologize for being so delinquent in my response; I was transfered and let this project languish for a while. Having finally come back to it, I am amazed at how much better your system works.

Everyone I've shown it to is quite interested, and after a few weeks of tinkering with it I started sharing it with a few friends of mine. The form works fine for everyone who has tried it, with one major exception:

when we go to print the form (which is rare but required on occasion) the dates are scrambled and become (with the exception of one or two fields) entirely wrong. We've tried this on various computers with different versions of Word and Word file types, all to the same effect. Do you have any idea why this might be happening?

I truly appreciate all of your help. I cannot tell you how much this form will make our jobs less tedious if we can just solve this final issue.

Sincerely,

Del

Hi Del,

I believe that has something to do with Word's 'update fields before print' setting. If it's checked, everything works OK. The attached update should work regardless of that setting.

Attached Files

Attendance Report Template.docx (24.8 KB, 81 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul,

Thank you SO MUCH for your assistance! I hope I am not testing your patience here by asking anything else, but I'm interested if you would mind explaining how you altered the formula to keep it from changing with a print job. The manual you linked to before was very helpful but I still feel like I know so little!

Finally (and if this is pushing the envelope, I completely understand if you prefer not to address it) I was curious if you knew how I might accomplish the following:

At the bottom right of the form is the date that the document is signed. I know of the simple date function that will put the date the form was last accessed in there automatically every time the document is opened. Our forms are submitted digitally, however (which is why I didn't discover the printing problem earlier), so automating this field doesn't make sense--UNLESS there is a way to keep it from updating after having the date entered the first time.

In other words, Joe Blow opens the document, completes it, and the form "knows" that he completed the form that day. He then saves it and sends it on its way. Is there a way to keep this last date on the page from changing after that (when its opened at headquarters and saved an additional time*)--perhaps by somehow tieing it to entering the "Week Ending" field that every other date is dependent on?

*The dates, once entered, will never be changed by headquarters.

I can't tell you how much I've enjoyed trying to figure this out and having your sage guidance when I (so often) hit a dead-end. This has been quite fun and extremely edifying. I really appreciate your work; if there's any way I can support it, please let me know!

Sincerely,

Del

PS I did check the "update fields before print" box just to see if that would fix the older form, but sadly it did not. The latest form you made up certainly works fine, but you already knew that!

Hi Del,

If you open both documents and press Alt-F9, the field coding they use wll be displayed. In the first form I posted, each date that was calculated use a full dat calculation field, just as the first one in the second form's table still does. I've simplified the 2nd-6th fileds in the second form's table by picking up the first one's julian date calculation, but that's not especially important. The real difference is that each of the calculation fields now uses a different set of bookmark names (created by the SET fields) for key parts of the calculation. That way, there's reduced cross-over of the calculations between the fields.

The signature date could be gathered by a formfield with an 'on-exit' macro that locks it after a valid date has been input, but then you'd need the supervisor's macro security set low enough to allow the macros to run and they'd need to either allow macros to run in each & every timesheet or open them only from a trusted location. Once you do that, though, every user is liable to get the macro security warning when the open the timesheet. There are possible workarounds in which only the supervisor has the macro, but implementing such a scheme would be somewhat problematic.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]