How to Create a Time Sheet that Fills Out Dates Automatically

**qualityoflife** · 12-10-2010, 01:33 AM

Hi,

I'm creating a time sheet. I'd like users to be able to enter a pay period (by number) and have the form auto-fill the dates. So far I have the following for the cell of the first date:

=IF(ISNUMBER(VLOOKUP(C6,Sheet1!A2:B27,2)),VLOOKUP(C6,Sheet1!A2:B27,2),"")

There are 26 pay periods of 2 weeks each. C6 is the number of the pay period. 'Sheet1' has the numbers of the pay period (1 to 26) listed in one column and the starting date of each pay period in the second column). I used ISNUMBER so that it wouldn't display an error if nothing was entered in cell C6.

It's working perfectly, but I wanted to make it a little fancier! Is there any way I could use the YEAR(NOW()) function so that I don't have to adjust 'Sheet1' in year 2011?

Thanks for any help!

**martindwilson** · 12-10-2010, 06:38 AM

depends where you start
here's one way to test instead of today() in g1 change to a future date to see the results

**royUK** · 12-10-2010, 06:57 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

How to Create a Time Sheet that Fills Out Dates Automatically

LinkBack

Thread Tools

Rate This Thread

Display

How to Create a Time Sheet that Fills Out Dates Automatically

Re: How to Create a Time Sheet that Fills Out Dates Automatically

Re: How to Create a Time Sheet that Fills Out Dates Automatically

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions