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)