Never ending time sheet

**Ltat42a** · 11-24-2008, 04:11 PM

Hello All,
We're using excel to generate a paper time sheet. The spreadsheet itself contains 26 worksheets in it - one for each of the 26 two week pay periods of the year.

Is it possible to make one worksheet that will work all year long, year after year after year? If for instance, our pay period starts on Sat 11/15 at 00:00, and ends 14 days later on Fri 11/28 at 24:00, the worksheet will enter the starting pay period date (11/15) in cell c3. Two weeks later, I open the spreadsheet, it determines that the next starting pay period is 11/29, it will enter 11/29 in cell c3.

just wondering.......Thanx

**mewingkitty** · 11-24-2008, 04:41 PM

Good question.
You could probably incorporate something with =TODAY() and have it figure the 2 week period around that date which it falls into within a boundary of a payday friday and a 14 day multiple period after it.

So that say, take Nov 15, consider 15/11/2008 + N(14), and let N = the number of 2 week periods which =TODAY() falls into after the date of Nov 15.

I just don't know how to do that

I'll let you know if I make any progress.

**Ltat42a** · 11-24-2008, 05:01 PM

Yeah, my thought too. At first, my thought was to use a 2nd worksheet with the starting pay period dates in it, then come up with a formula that would select the next date after =today(). I don't know how to write that or if it would work. Maybe something with VBscript would work, thing is, I can spell VBscript - that's all I know about it.

Thanx..

**mewingkitty** · 11-24-2008, 05:36 PM

I usually work with times and straight numbers, kinda new to the date system in Excel, but it seems like the CEILING and FLOOR commands might work in this circumstance, I've got it to read out two week periods, but they're the wrong 2 weeks, seems that using 14 as your number of significance falls on the opposite fridays than the ones you want them too!

mm, I left the date in the top left on the spreadsheet as a date, instead of =TODAY() but that's kinda the route I figured would work. Could just take CEILING for example and subtract between 1 and 13 from that answer to get the rest of the days of the pay period. It works, just for the wrong Saturdays :P

**Ltat42a** · 11-25-2008, 06:56 AM

Thanx for the attempt, I'll keep trying.

LT

**royUK** · 11-25-2008, 08:16 AM

It would be helpful to see what you have so far.

**Ltat42a** · 11-25-2008, 10:38 AM

Here's a sample. I deleted most of the worksheets that we normally use. I wish we could just use the first two worksheets and delete the rest. If we can get to work, it would save us from having manipulate this every year after year after year.

Thanx...LT

**royUK** · 11-25-2008, 10:45 AM

The workbook is password protected!

**Ltat42a** · 11-25-2008, 12:31 PM

Arghh! Sorry my bad, ........here

**royUK** · 11-25-2008, 12:58 PM

What about this slight change - uses VLOOKUP, Data Validation and Named Ranges

TimeSheet

**Ltat42a** · 11-25-2008, 01:21 PM

Thanx royUK, this just might work. I'll run it across the powers that be and see what happens.

...LT

**royUK** · 11-26-2008, 04:52 AM

Post back & let us know

**Ltat42a** · 11-26-2008, 06:34 AM

Ok, will do.

Can I ask....how did you set that up?

Thanx...LT

**royUK** · 11-26-2008, 06:42 AM

Originally Posted by Ltat42a

Ok, will do.

Can I ask....how did you set that up?

Thanx...LT

I created Named Ranges - in the top left above the Column Numbers is the Name Box, use the drop down to see the Names or Insert>-Name>-Define.

I added a column for the Period Numbers.

The VLOOKUP looks up the period number selected in the sheet Personnel Data in the table of periods it then adds that date to the timesheet as the first date.

The selection is made using Data Validation

There's more info on Data validation here

http://www.excel-it.com/excel_tutorials.htm

And VLOOKUP here

http://www.excel-it.com/excel_functions.htm

Please rate the answers using the Scales icon