Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill Pay Periods
There must be a post already with answers but I can't seem to locate it so I
need some help. I have an Excel Spreadsheet where I have field called Pay Period and would be 1-26 for the year. How do I set up the sheet so that when I enter a specific Pay Period, fields for Report Dates_________ through ______________ are filled with the correct 14 day period for that Pay Period entered? For example if Pay Period 25 the fields would be 11/27/05 and 12/10/05. -- Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill Pay Periods
Hi
I would hold start of Payroll year in a cell on the Sheet,say A1, with 12/12/04 And, supposing you enter the Pay period number in B1, 25 Then in the cell holding the heading you enter ="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through "&text(A1+B1*14+14,"mm/dd/yy") Alternatively, instead of holding the startdate on th sheet, InsertNameDefine Name Startdate Refers to =12/12/04 Substitute the word stardate for A1 in the 2 references in the above formula. Regards Roger Govier bdehning wrote: There must be a post already with answers but I can't seem to locate it so I need some help. I have an Excel Spreadsheet where I have field called Pay Period and would be 1-26 for the year. How do I set up the sheet so that when I enter a specific Pay Period, fields for Report Dates_________ through ______________ are filled with the correct 14 day period for that Pay Period entered? For example if Pay Period 25 the fields would be 11/27/05 and 12/10/05. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill Pay Periods
I am still confused. I am confused about cell holding the heading?
Then in the cell holding the heading you enter ="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through "&text(A1+B1*14+14,"mm/dd/yy") Can you explain in more detail? I have two fields that need data to appear. The Report Date and the Through Date. Brian "Roger Govier" wrote: Hi I would hold start of Payroll year in a cell on the Sheet,say A1, with 12/12/04 And, supposing you enter the Pay period number in B1, 25 Then in the cell holding the heading you enter ="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through "&text(A1+B1*14+14,"mm/dd/yy") Alternatively, instead of holding the startdate on th sheet, InsertNameDefine Name Startdate Refers to =12/12/04 Substitute the word stardate for A1 in the 2 references in the above formula. Regards Roger Govier bdehning wrote: There must be a post already with answers but I can't seem to locate it so I need some help. I have an Excel Spreadsheet where I have field called Pay Period and would be 1-26 for the year. How do I set up the sheet so that when I enter a specific Pay Period, fields for Report Dates_________ through ______________ are filled with the correct 14 day period for that Pay Period entered? For example if Pay Period 25 the fields would be 11/27/05 and 12/10/05. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill Pay Periods
Ok I got what your say now as I can have all that Text come from one field
across the spreadsheet and can insert spaces to move it as I need to fill in the areas. Now the issue I have is that I lost the ability to show every day of the week with the date as I used to key off the the Through Date Field by cell number. Unfortunately now with the heading field I lost that field to key on. What can I use now to have the 14 days of the week show the correct date in a field next to the Day of the week whenever the Pay Period is changed and not lose the formula you supplied that does work? -- Brian "Roger Govier" wrote: Hi I would hold start of Payroll year in a cell on the Sheet,say A1, with 12/12/04 And, supposing you enter the Pay period number in B1, 25 Then in the cell holding the heading you enter ="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through "&text(A1+B1*14+14,"mm/dd/yy") Alternatively, instead of holding the startdate on th sheet, InsertNameDefine Name Startdate Refers to =12/12/04 Substitute the word stardate for A1 in the 2 references in the above formula. Regards Roger Govier bdehning wrote: There must be a post already with answers but I can't seem to locate it so I need some help. I have an Excel Spreadsheet where I have field called Pay Period and would be 1-26 for the year. How do I set up the sheet so that when I enter a specific Pay Period, fields for Report Dates_________ through ______________ are filled with the correct 14 day period for that Pay Period entered? For example if Pay Period 25 the fields would be 11/27/05 and 12/10/05. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill Pay Periods
Hi
The formula =A1+B1*14 gives your start date =A1+B1*14+14 gives your end date dependent upon where you are storing your Payroll year Start date and where you are entering your Period number. Just insert these formulae into the cells you originally had dates in. Regards Roger Govier bdehning wrote: Ok I got what your say now as I can have all that Text come from one field across the spreadsheet and can insert spaces to move it as I need to fill in the areas. Now the issue I have is that I lost the ability to show every day of the week with the date as I used to key off the the Through Date Field by cell number. Unfortunately now with the heading field I lost that field to key on. What can I use now to have the 14 days of the week show the correct date in a field next to the Day of the week whenever the Pay Period is changed and not lose the formula you supplied that does work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Auto Fill | Excel Discussion (Misc queries) | |||
stop auto fill from thinking for itself!!!!!!! | New Users to Excel | |||
Auto Fill Options | Excel Discussion (Misc queries) | |||
Auto fill option box disappeared | Excel Worksheet Functions | |||
Auto Fill Options | Excel Discussion (Misc queries) |