Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Calculation - Continuation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Calculation - Continuation
Don't know how you're validating, but one way:
Pay period start: =MOD(<date-DATE(2006,12,17),14)=0 Pay period end: =MOD(<date-DATE(2006,12,17),14)=13 as long as the periodicity of your pay periods (i.e., every 2 weeks beginning on Sunday, 12/17/2006) doesn't shift, this will work forever. In article , TimJames wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Calculation - Continuation
I really don't see the problem with just pulling down what you have ... you
would be good for over 200 years, and I don't think they will still be using the spreadsheet then! But, In the workbook open event, you can check if Now is bigger than the max of column, and if so, replaced the first value with the last value. Bob "TimJames" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Calculation - Continuation
I was thinking, why not just supply the start date and calculate the
valid pay period beginning and ending from it: Private Sub NextPayPeriod() Dim dStart As Date, dToday As Date Dim dNextPayStart As Date, dNextPayEnd As Date Dim iDays As Integer, iPayPeriodLength As Integer 'set the start date dStart = "12/17/2006" 'get today's date dToday = Now() 'set the length of the pay period in days iPayPeriodLength = 14 'divide the number of days between today and the start date 'by the number of days in the pay period (14) and get the 'remainder using Mod iDays = (dToday - dStart) Mod iPayPeriodLength 'find the beginning of the current pay period and then add 'the pay period days + 1 to get the start of the next period dNextPayStart = (dToday - iDays) + (iPayPeriodLength + 1) 'add the pay period days to the previous date to get the 'end of the next one dNextPayEnd = dNextPayStart + iPayPeriodLength 'display it MsgBox "Next period begins on " & Format(dNextPayStart, "MM/dd/ yyyy") & _ vbNewLine & "and ends on " & Format(dNextPayEnd, "MM/dd/yyyy") End Sub Cory On Oct 31, 12:12 pm, TimJames wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Continuation of a macro | Excel Discussion (Misc queries) | |||
Line Continuation issue | Excel Worksheet Functions | |||
Macro Continuation | Excel Programming | |||
continuation from yesterday | Excel Discussion (Misc queries) | |||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend? | Excel Programming |