Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date calculation
I have a column of dates in Column A that generate with a formula like so:
A1 "=O18" (This is currently a date that is inputted by the user. a2 "=b1+1" Column b is like: b1 "=a1+13" b2 "=a2+13" (Quotes are used here to indicate the formula I am using in the cells) These formulas go down about 57 cells. I would like to automate this so that when the last cell is equal to (Or greater than) today, cell A1 will automatically change to the date in the last cell. Any help in how to do this would be greatly appreciated! Thanks, Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date calculation
Hi Tim,
Please provide more information. Which part specifically do you want automated? The complete process? If so, 1. What are the column headings for columns a and b? 2. When is it time to add 13 to the value in column A? 3. These formulas go down about 57 cells< How is this useful to your process? Dan How does the end user know what date to enter. What does Column b is like: b1 "=a1+13" b2 "=a2+13" (Quotes are used here to indicate the formula I am using in the cells) These formulas go down about 57 cells. I would like to automate this so that when the last cell is equal to (Or greater than) today, cell A1 will automatically change to the date in the last cell. Any help in how to do this would be greatly appreciated! Thanks, Tim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date calculation
This all part of a timesheet for the county in which I work. The column
headings would be For column 'A' Start of the pay period, for column 'B' End of the pay period. The 52 cells (I checked this time) are just a list of the appropriate dates for the timesheet pay period, which goes for two weeks. So I generate these numbers and use them as a reference when the employees fill out the timesheets to ensure that they are "starting" and "ending" on the correct dates for the pay periods. These 'references' are actually good for two years. I wanted to automate it so that at the end of the two years the employees wouldn't have to remember to update that one date field that starts the entire generation of pay period dates. I hope that was clear enough. Sometimes when you know what you want and how an inherited spreadsheet works, it isn't easy to explain it to someone else. Thanks again, Tim "dan dungan" wrote: Hi Tim, Please provide more information. Which part specifically do you want automated? The complete process? If so, 1. What are the column headings for columns a and b? 2. When is it time to add 13 to the value in column A? 3. These formulas go down about 57 cells< How is this useful to your process? Dan How does the end user know what date to enter. What does Column b is like: b1 "=a1+13" b2 "=a2+13" (Quotes are used here to indicate the formula I am using in the cells) These formulas go down about 57 cells. I would like to automate this so that when the last cell is equal to (Or greater than) today, cell A1 will automatically change to the date in the last cell. Any help in how to do this would be greatly appreciated! Thanks, Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date calculation
Hi Tim,
I'm still not clear what you want to automate. Is this a timesheet you use or the employees use? I generate these numbers and use them as a reference when the employees fill out the timesheets to ensure that they are "starting" and "ending" on the correct dates for the pay periods. Why not just copy your formulas down another 52 rows? These 'references' are actually good for two years. I wanted to automate it so that at the end of the two years the employees wouldn't have to remember to update that one date field that starts the entire generation of pay period dates. Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date calculation
This is a timesheet that the employees use.
To ensure that the employees are using the correct dates there is a hidden worksheet that lists out the beginning dates for the pay periods and the end dates for the pay periods. The information that is currently listed is good for two years, but at the end of the two years the dates will be wrong and most (If not all) of the employees do not know that they should update the reference date that begins this entire pay period generation. My dates that I would like to update automatically upon their expiration look like this: Pay Period Start Pay Period End 12/17/2006 12/30/2006 12/31/2006 01/13/2007 01/14/2007 01/27/2007 01/28/2007 02/10/2007 And so on. The last dates a 11/30/2008 12/13/2008 So when today's date is equal to or less than that last date in the first column (In my example it would be 11/30/2008) I want that date to move to the top of the column and replace the originating date (In my example the originating date is 12/17/2006) and that would result in all of the other dates automatically updating and the pay period dates would be good for another two years. I don't expand my dates from two to four years because I would just be putting off the problem instead of implementing a solution. Thanks for being patient and for all of your help! -Tim "dan dungan" wrote: Hi Tim, I'm still not clear what you want to automate. Is this a timesheet you use or the employees use? I generate these numbers and use them as a reference when the employees fill out the timesheets to ensure that they are "starting" and "ending" on the correct dates for the pay periods. Why not just copy your formulas down another 52 rows? These 'references' are actually good for two years. I wanted to automate it so that at the end of the two years the employees wouldn't have to remember to update that one date field that starts the entire generation of pay period dates. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A specific date used for calculation of a new date | Excel Discussion (Misc queries) | |||
Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
End Date Calculation (adding a start date duration) | Excel Discussion (Misc queries) | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend? | Excel Programming |