JohnnyBravo
01-02-2006, 05:56 PM
Happy new year and good tidings to the fine people here at VBAX! http://vbaexpress.com/forum/images/smilies/023.gifEver since I came here you guys have been nothing but helpful and I am very grateful for all your help.
Although I've been using MS office applications for many years, I'm very new to VBA and know very little. A couple of months ago, I posted a question on how to get started with VBA or how to learn the basics and someone recommended that I buy a "VBA For Dummies" book. So I did.
A user on a computer forum asked how to update a particular cell every other Friday and it really got me curious. Although, I've been trying to come up with a solution, the book I have has next to nothing that covers this particular situation.
Basically the user has a timecard sheet setup in Excel when the employees record the time they came in and time they leave each day. The company is a bi-weekly payroll schedule so the "payperiod ending" date changes every other FRIDAY.
See the screenshot below of the scenario.
http://i5.photobucket.com/albums/y182/tushman/ScreenHunter_029.jpg
I've found the following exmaple on the internet how to update a date on a weekly basis but it doesn't work for a bi-weekly basis.
=TODAY()-MOD(TODAY()-2,7)+7
I figure if I'm going to start learning VBA - I have to start somewhere and this seemed like a challenging but easy enough project.
Basically my idea is to have VBA examine the pay period ending date (Cell C5) and run a If then argument.
So upon opening the worksheet, have a routine that examines cell C5, and compare it to the current date. If current date is more than 13 days past cell C5; replace it with a new date that is 2 weeks from the old one. If it's 13 days or less, then do nothing.
Would this be the most efficient approach? If so, can someone please get me started?
Although I've been using MS office applications for many years, I'm very new to VBA and know very little. A couple of months ago, I posted a question on how to get started with VBA or how to learn the basics and someone recommended that I buy a "VBA For Dummies" book. So I did.
A user on a computer forum asked how to update a particular cell every other Friday and it really got me curious. Although, I've been trying to come up with a solution, the book I have has next to nothing that covers this particular situation.
Basically the user has a timecard sheet setup in Excel when the employees record the time they came in and time they leave each day. The company is a bi-weekly payroll schedule so the "payperiod ending" date changes every other FRIDAY.
See the screenshot below of the scenario.
http://i5.photobucket.com/albums/y182/tushman/ScreenHunter_029.jpg
I've found the following exmaple on the internet how to update a date on a weekly basis but it doesn't work for a bi-weekly basis.
=TODAY()-MOD(TODAY()-2,7)+7
I figure if I'm going to start learning VBA - I have to start somewhere and this seemed like a challenging but easy enough project.
Basically my idea is to have VBA examine the pay period ending date (Cell C5) and run a If then argument.
So upon opening the worksheet, have a routine that examines cell C5, and compare it to the current date. If current date is more than 13 days past cell C5; replace it with a new date that is 2 weeks from the old one. If it's 13 days or less, then do nothing.
Would this be the most efficient approach? If so, can someone please get me started?