I am trying to put together a Comp Time tracker for use around our office for people to track their comp time individually for salaried workers that work over 40 hours. In cell A1 is =today() so that each time the spreadsheet is opened it displays today's date.
There are 4 column headings (A2:D2) and are labeled thus:
Week Number / Week Ending / Hours Accrued / Hours Used
There are then 26 rows below these column headings (A3:D28) representing 26 weeks or 6 months of data.
The end goal is to have input data for Hours Accrued and Hours Used to roll off of the spreadsheet as they expire to keep a rolling tally of what comp time is remaining by subtracting the sum of the inputs from range D3:D28 (Hours Used) from range C3:C28 (Hours Accrued) giving a constant result of hours remaining.
In cell B3 under Week Ending, the first week ending input is: =A1+7*1-WEEKDAY(A1+7-7) hearkening back to today's date =today() from A1 and giving me the Saturday at the end of the current work week. B4 is=B3-7, to give me the date of 7 days prior or the Saturday date of B3. This is copied down to B28 to give me all of the last 26 Saturday's in descending sequence.
In cell A3 is =WEEKNUM(B3). This is copied down to A28 to give me week numbers of the year for the Saturdays that each work week ends on.
I'm trying to find a formula to associate the manual inputs with a their associated week number that will move down the list as the =today() in cell A1 is updated as the days and weeks pass until it expires after 26 weeks at which time the week number and it's accompanying manual inputs of hours are rolled off the sheet or deleted.
I'm also trying to populate 0.00 automatically in the cells for Hours Accrued and Hours Used until they are changed by the user with their own overtime hourly input.
I am looking for formulas to solve this however I am slightly familiar with the Visual Basic Editor, so I am open to a solution in that manner.
There are 4 column headings (A2:D2) and are labeled thus:
Week Number / Week Ending / Hours Accrued / Hours Used
There are then 26 rows below these column headings (A3:D28) representing 26 weeks or 6 months of data.
The end goal is to have input data for Hours Accrued and Hours Used to roll off of the spreadsheet as they expire to keep a rolling tally of what comp time is remaining by subtracting the sum of the inputs from range D3:D28 (Hours Used) from range C3:C28 (Hours Accrued) giving a constant result of hours remaining.
In cell B3 under Week Ending, the first week ending input is: =A1+7*1-WEEKDAY(A1+7-7) hearkening back to today's date =today() from A1 and giving me the Saturday at the end of the current work week. B4 is=B3-7, to give me the date of 7 days prior or the Saturday date of B3. This is copied down to B28 to give me all of the last 26 Saturday's in descending sequence.
In cell A3 is =WEEKNUM(B3). This is copied down to A28 to give me week numbers of the year for the Saturdays that each work week ends on.
I'm trying to find a formula to associate the manual inputs with a their associated week number that will move down the list as the =today() in cell A1 is updated as the days and weeks pass until it expires after 26 weeks at which time the week number and it's accompanying manual inputs of hours are rolled off the sheet or deleted.
I'm also trying to populate 0.00 automatically in the cells for Hours Accrued and Hours Used until they are changed by the user with their own overtime hourly input.
I am looking for formulas to solve this however I am slightly familiar with the Visual Basic Editor, so I am open to a solution in that manner.
