Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
I looked through all the message threads before asking this question.
Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
achapman -- this is kind of brute force and maybe someone will have something
more elegant. But here's one way, involving lookup tables. First, on some place that won't show up on the timecard, create a little table as follows. I'm putting it out at $Y$1:$Z$7, just as an example Y Z 1 1 Monday 2 2 Tuesday 3 3 Wednesday etc. Then A B 1 Pay Period Ending 5/20/06 2 3 =b1-15 =b1-14 4 =VLOOKUP(WEEKDAY(A3),$Y$1:$Z$7,2) tried it. think it works. HTH "achapman" wrote: I looked through all the message threads before asking this question. Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
Cell A1 has your Pay Ending Date
In cell A3 create the formula: =A1-14 In cell B3 create the formula: =A1-13 In cell B4 create the formula: =A1-12 and so on In cell A4 create the formula: =TEXT(A3,"ddd") In cell B4 create the formula: =TEXT(B3,"ddd") In cell C4 create the formula: =TEXT(C3,"ddd") and so on "achapman" wrote: I looked through all the message threads before asking this question. Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
OK -- thought of a more elegant way on the drive home. Instead of the
VLOOKUP formula in B4, use: =CHOOSE(WEEKDAY(B3),"Sunday","Monday","Tuesday", etc.) Tried it. Works good. HTH "pdberger" wrote: achapman -- this is kind of brute force and maybe someone will have something more elegant. But here's one way, involving lookup tables. First, on some place that won't show up on the timecard, create a little table as follows. I'm putting it out at $Y$1:$Z$7, just as an example Y Z 1 1 Monday 2 2 Tuesday 3 3 Wednesday etc. Then A B 1 Pay Period Ending 5/20/06 2 3 =b1-15 =b1-14 4 =VLOOKUP(WEEKDAY(A3),$Y$1:$Z$7,2) tried it. think it works. HTH "achapman" wrote: I looked through all the message threads before asking this question. Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
I think this does it. I don't see a way to keep the Sunday - Saturday
rotation look consistent through the spreadsheet. She wanted just the date to be changed, but this should work. Thanks so much! "jcamacho" wrote: Cell A1 has your Pay Ending Date In cell A3 create the formula: =A1-14 In cell B3 create the formula: =A1-13 In cell B4 create the formula: =A1-12 and so on In cell A4 create the formula: =TEXT(A3,"ddd") In cell B4 create the formula: =TEXT(B3,"ddd") In cell C4 create the formula: =TEXT(C3,"ddd") and so on "achapman" wrote: I looked through all the message threads before asking this question. Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Population
Well, she does want to keep the Sunday through Saturday lineup. In other
words, the dates will always start with the Sunday about 2 weeks prior to the pay period, just like below: 5/7 5/8 5/9 5/14 5/15 5/16 Sun Mon Tues.... Sun Mon Tues... So that only the date changes, not the days of the week. Is this even possible? "achapman" wrote: I think this does it. I don't see a way to keep the Sunday - Saturday rotation look consistent through the spreadsheet. She wanted just the date to be changed, but this should work. Thanks so much! "jcamacho" wrote: Cell A1 has your Pay Ending Date In cell A3 create the formula: =A1-14 In cell B3 create the formula: =A1-13 In cell B4 create the formula: =A1-12 and so on In cell A4 create the formula: =TEXT(A3,"ddd") In cell B4 create the formula: =TEXT(B3,"ddd") In cell C4 create the formula: =TEXT(C3,"ddd") and so on "achapman" wrote: I looked through all the message threads before asking this question. Hopefully, I didn't miss the answer if it has already been stated. I have a timesheet. A1 is the "Pay Period Ending x/xx/xxx". I want it so that when someone enters a date in A1, the date appears in the cells above the cells labeled Sunday - Saturday. I have 3 weeks in the timesheet because pay periods are by the date and not the day of the week. We want to make it easy for the employees by having the date automatically populate by the pay period date. It should look like the following: Pay Period Ending 5/20/06 5/7 5/8 5/9 5/10 5/11 Sun Mon Tue Wed Thur etc. In other words, whatever the date, the calculation will start with the Sunday at least 2 weeks prior... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Adding XY days to date in cells | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions | |||
Date Math Problem | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |