Increment dates

Hi there,

I'm sure this is simple, but I haven't found a simple solutions.

I'm creating a new timesheet for my company and I want it to populate
automatically.

I have a field where the payroll manager can enter the period start
date.  From that, I want to fill in all of the dates for the payroll.

What I'm looking for is an easy way to lookup the date value in the
period start date field and then increment the dates in the body of the
worksheet.

What I have so far is something like this.

For the first day of the payperiod:  =I$6
For the 2nd day of the payperiod: = I$6 + 1
For the 3rd day of the payperiod:  = I$6 + 2

And so on.  However, this is a tedius method of building up the date
range I want.  It works, but I'm looking for something a little more
elegant.  

Basically, I think I need a macro or something that does something like
that that can automatically increment the dates for me in the range I
want.

Can anyone point me in the right direction for writing something like
that?  (I haven't worked much with Macros or VBA).

Kim


-- 
kswinth
------------------------------------------------------------------------
kswinth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24646
View this thread: http://www.excelforum.com/showthread.php?threadid=383191

0
6/29/2005 3:18:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
364 Views

Similar Articles

[PageSpeed] 57

Kim

It's not clear what you want to make easier.  Something like select a range, 
click a button and it sets it up?

The formula you have looks good as you can simply change one date and the 
rest will reflect

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"kswinth" <kswinth.1reb2h_1120061128.4013@excelforum-nospam.com> wrote in 
message news:kswinth.1reb2h_1120061128.4013@excelforum-nospam.com...
>
> Hi there,
>
> I'm sure this is simple, but I haven't found a simple solutions.
>
> I'm creating a new timesheet for my company and I want it to populate
> automatically.
>
> I have a field where the payroll manager can enter the period start
> date.  From that, I want to fill in all of the dates for the payroll.
>
> What I'm looking for is an easy way to lookup the date value in the
> period start date field and then increment the dates in the body of the
> worksheet.
>
> What I have so far is something like this.
>
> For the first day of the payperiod:  =I$6
> For the 2nd day of the payperiod: = I$6 + 1
> For the 3rd day of the payperiod:  = I$6 + 2
>
> And so on.  However, this is a tedius method of building up the date
> range I want.  It works, but I'm looking for something a little more
> elegant.
>
> Basically, I think I need a macro or something that does something like
> that that can automatically increment the dates for me in the range I
> want.
>
> Can anyone point me in the right direction for writing something like
> that?  (I haven't worked much with Macros or VBA).
>
> Kim
>
>
> -- 
> kswinth
> ------------------------------------------------------------------------
> kswinth's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=24646
> View this thread: http://www.excelforum.com/showthread.php?threadid=383191
> 


0
6/29/2005 7:33:07 PM
On Wed, 29 Jun 2005 10:18:02 -0500, kswinth
<kswinth.1reb2h_1120061128.4013@excelforum-nospam.com> wrote:

>
>Hi there,
>
>I'm sure this is simple, but I haven't found a simple solutions.
>
>I'm creating a new timesheet for my company and I want it to populate
>automatically.
>
>I have a field where the payroll manager can enter the period start
>date.  From that, I want to fill in all of the dates for the payroll.
>
>What I'm looking for is an easy way to lookup the date value in the
>period start date field and then increment the dates in the body of the
>worksheet.
>
>What I have so far is something like this.
>
>For the first day of the payperiod:  =I$6
>For the 2nd day of the payperiod: = I$6 + 1
>For the 3rd day of the payperiod:  = I$6 + 2
>
>And so on.  However, this is a tedius method of building up the date
>range I want.  It works, but I'm looking for something a little more
>elegant.  
>

You could just refer back to each preceding cell and use the formula "preceding
cell" +1.

Or if your subsequent payperiod dates are in adjacent columns; you could use a
formulat of the type:  =I$6 + COLUMN()-n  where n is the column number of your
first column of data.  The same principle can be used if the dates are in rows.

Then you simply copy/drag the formula down as needed.


--ron
0
ronrosenfeld (3122)
6/29/2005 11:01:33 PM
Reply:

Similar Artilces:

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

Expirary date for gift certificates....
Good day, Is there anyway in which I can assign expirary dates on gift certificates... Best regards, Uttam Uttam, If you have them setup as a Voucher type, you can set the expiration period in SOM | File | Config | Tender Marc "uttam" <uttam@discussions.microsoft.com> wrote in message news:5C9E4DCC-8D84-4DCF-BFB5-A6DC9B399E3A@microsoft.com... > Good day, > Is there anyway in which I can assign expirary dates on gift > certificates... > > Best regards, > Uttam Thanks marc.... "Marc" wrote: > Uttam, > > If you have t...

Exporting Date received and Date Sent to Access
How can export both 'date received' and 'date sent' fields to another program such as Access or Excel? However, if you use Access to set up a linked table connected to your = mail folder, you will see the Received date among the available = properties. (I don't remember whether Sent is also shown.) Third-party = export tools are also available; see = http://www.outlookcode.com/d/customimport.htm#tools --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jump...

Comparing Dates with todays date
Hi I wonder if anyone could help me. I am trying to look at the date entered in a cell e.g. (in cell B2 05/02/2010). I want excel to look at the date in B2 and if the date as passed then for cell C2 to turn red. Can anyone help? Hope this makes sense. Conditional Formatting/ Formula Is/ =B2<TODAY() -- David Biddulph housinglad wrote: > Hi > > I wonder if anyone could help me. > > I am trying to look at the date entered in a cell e.g. (in cell B2 > 05/02/2010). I want excel to look at the date in B2 and if the date > as passed then for cel...

incrementing numbers
Can anyone give me a tip on setting up a macro that will increment numeric cell each time it is run. i.e. an invoicing sheet that wil print a new number each time. probably easy heh!, I must be missing something. Thanks in advance ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Hi Public Sub AddIntoA1() Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1 End Sub -- (When sending e-mail, use address ar...

date error
hi i have 2 problem. 1) iam using a vlookup formula, but when there is no value in the cell the cell shows the ( 0 for date and jan for month) 0-jan. but i want to be 0 instead of the 0-jan, why its happening, 2) i have a database upto 2000 subscriber paying monthly sub. some customer pays twice in month. i used vlookup formula . it shows only one date , is there possible to show all the date in one cell, so i can know how many times that customer paid the subs in these month. if instead if vlookup i used the sumif formula its add the date and i got the wrong date. so pls help. rega...

Changing ruler increments?
Is there a way to change the ruler increments, thereby changing the snap to increments or does it just default to the 16th of an inch only? I'm trying to make all text carrying boxes the same size and can come close but can't snap them into the same size. It's really close enough but I was just wondering if I can make the snaps and nudge increments smaller than they ar Thanks Hi Catt (anonymous@discussions.microsoft.com), in the newsgroups you posted: || Is there a way to change the ruler increments, thereby changing the || snap to increments or does it just default to the 16th...

Return latest date in range?
I have a named range, with each row in the range having a repeating 8- column data block (date, mode, three entered numbers, three calculated numbers). Every time data is entered in one of these rows, it gets tacked onto the end of whatever is in that row, and will always be the same 8-column format. Not all rows have the same number of 8-column entries. Is there a formula I can enter in one cell at the top of the sheet that will return the latest date in this entire range? Or would I have to use a macro to search all dates in the range? Ed Assuming your dates are in column A rows 2 to 1...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

date conversion #2
I am trying to import a file that has dates on it in this format yy-mm-dd. When excel receives the dates it converts them to standard mm/dd/yyyy . Unfortunately the mm shown is actually the yy and the dd shown is the mm and the yyyy shown is the day. Example: Import 03-04-24 Result 3/4/2024 Dose anybody have any ideas on how I can fix this. Thanks in advance. No sorry Jeff, but I have pretty much the same problem at the moment. I am trying to put in a bank sort code which is numbered like ##-##-## and it keeps converting it into date format but dd/mm/yyyy or mm/dd/yyyy,...

I want to calculate a date 1 year ahead of a date in a bookmark
I want to be able to calculate a new date 1 year ahead of a date that is stored or entered in a bookmark so that if the value under the bookm ark changes the calculated date value will also change. In Excel I can easily do date calculation but if it's available in Word help is NO help. Terry, See if something like this would work. Sub DateReCalc() Dim strOldDate As String Dim dteNewDate As Date strOldDate = ActiveDocument.Bookmarks("origDate").Range.Text dteNewDate = DateAdd("yyyy", 1, DateValue(strOldDate)) MsgBox dteNewDate End Sub Steve Y...

compare date
Hi, I have a worksheet with different tabs for the different sales people. I'm trying to follow up the price offers they make. So in column A there is the name of the customer, in column B is the due date for the price offer. Column C will be the date of the actual price offer. What I would like to do is to compare the dates in column B with the Today-date and if a or some dates in column B are equal to or past the Today-date, get a message box allerting me there are price offers due or overdue. The best should be if this comparison could be done on opening the Excel-worksheet (for...

Searching for Date Range
I'd like to set up a function to allow the user to search for files based on a due date. I would like it so that the user can search for files due in the following three weeks of the current date. Does anyone know how I would be able to set this up? Use BETWEEN DateAdd("ww", -3, Date) AND Date as the criteria for your date field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <jtshockey@gmail.com> wrote in message news:1194892751.925372.73750@e9g2000prf.googlegroups.com... > I'd like to set up a function to allow the user ...

Date range on chart includes dates not on worksheet
Pulling my hair out! I've got a worksheet recording student progress--so there are no weekend or holiday dates. But when I make a bar chart and copy the appropriate date series into the "Category (X)axis labels" field, it inserts all the weekend and holiday dates as well. And, of course, creates 0-value bars. What am I doing wrong? Tnamks in advance. The x-axis has defaulted to a time-scale. To change the setting: Select the chart Choose Chart>Options Select the Axes tab For Category (X) axis, choose Category, click OK Peter wrote: > Pulling my hair out! I&...

Why does this not work (selecting a date) ?
SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' & Year(RecordDate)) AS RecDate This converts 8/31/2007 to 8/1/2007 Works fine for me with 2003 SP2. You omitted a closing parenthesis. -- KARL DEWEY Build a little - Test a little "mscertified" wrote: > SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' & > Year(RecordDate)) AS RecDate > > This converts 8/31/2007 to 8/1/2007 Where is the missing parentheses? I count 4 opening and 4 closing??? "KARL DEWEY" wrote: >...

increments
I have values in cells f5 and h5.These values range from 1.01 to 1000,but are in bands, and there only allowed increments in each band. Min max increment 1.00 2.00 0.01 2.02 3.00 0.02 3.05 4.00 0.05 4.10 6.00 0.10 6.20 10.00 0.20 10.50 20.00 0.50 21.00 30.00 1.00 32.00 50.00 2.00 55.00 100.00 5.00 110.00 1000.00 10.00 Thus allowed values are 1.01,1.02,-----,2.02,2.04-----,3.05,3.10,3.15 etc. In cell n5 I want create a condition that will carry out an action if 1.the difference between the values in h5 and f5 is greater than two or more increments and 2.f5 has A value less than 10. This ac...

Chart axis custom date format
Dear all! Excel 2003, charting. On Y axis I have scale from 0 to 7, and when I go to "format axis- number-custom" I can input formula "[Blue][<4]#.###,0;[Red][>4]#.###, 0;#.###,0" which colors the scale as I would like. On X axis I have dates (1.jan.06, 1.feb.06, 1.mar.06....1.jun.08). Is it possible to write similar formula as above on x axis - for instance if date is less than 1.jan.07 then color it red, if less than 1.jan 08 than color it blue? This would allow to have on X axis only date format "mmm" and the colors would help differentiate between yea...

Transcation date
I have money 2004. I have a problem with the program changing the all entry date on the check book (checks, debit card) to the date of the transcation date that I get from my bank when I download the information. And If I print a copy of the check book to review later I can not tell if a transcation has cleared or not. Can you help me solve those two problems. Thank you Pecola Hamilton I'm not sure about the second, but for the first, you need to uncheck the option in tools->options->online services, 'overwrite transaction dates with dates from downloaded transacti...

Counter/Incrementing
I have a cell that we increment by one every day. For example: Today the number is: 1 Tomorrow we will set the number to: 2 Day after tomorrow it will be: 3 and so on... The cell doesn't 'count' anything, it's just a number that increments by one with each new day. Any formulas that will automate this? If the couter starts on the 01 may 04: =TODAY()-38108 and format as number or general. Dunca -- Message posted from http://www.ExcelForum.com Hi M, try =TODAY()-38117 Format the cell as general HTH >-----Original Message----- >I have a cell that we increment b...

Calcualte date and time
Hi Please help me how to calculate the number of hours and minutes Between two columns: Ex: Beginning Apt. Ending Apt. 2/1/10 9:40am and 2/1/10 2:00pm. It will be 4 hours and 20 minutes or (4:20) if I calculate manually. Please help me to write the formula. Thanks Chi If your beginning time is in A1, and ending time in A2, the formula is: =a2-a1 Format as a time. Regards, Fred "Chi" <Chi@discussions.microsoft.com> wrote in message news:44CD6F0D-F59D-4FAF-B331-F45FFC2EBF41@microsoft.com... > Hi > > Please help me h...

DAYS360 where Start Date is Static and End Date is Latter of Two Dates
Hello! I've spent over two hours trying to figure out how to get this done right. Column C is the Purchase Order Release Date Column D is the Goods Receipt Post Date Column E is the Invoice Receipt Post Date Column C will always be the start date when using DAYS360, however, since the Goods Receipt Date sometimes preceeds the Invoice Receipt Date (and vice versa), I need DAYS360 (or an equivalent function) to calculate the number of days from Column C Row 1 to the *LATTER* of the dates found in COLUMN D Row 1 and COLUMN E Row 1. Any assistance would be sooo appreciated. Thank...

Update: Date
Hi, I have a speadsheet that I need to track when it was last used. In cel A1 is the "Last Reviewed", In cell B2 is a date. Is there any way when saving / closing the sheet that the B2 date is updated, so that when you open it, you can see the last date it was opened. I have tried =TODAY() ect but that keeps updating the current date. Any thoughts ? Thanks. Duncan. On Feb 24, 6:53=A0am, Decreenisi <toyoda....@googlemail.com> wrote: > Hi, > > I have a speadsheet that I need to track when it was last used. In cel > A1 is the "Last Reviewed", In cell B2 ...

Date Last Modified
I have created a Word (2002) document in which I have inserted a field which picks up the document properties. I have inserted the date the document was last modified, however, when I modify the document and save it, it does not appear to automatically change the date/time. Can anyone let me know if this date can be forced to automatically change if the document is modified? TIA Note that if the field is in the header or footer, it will update when you open the document. To trigger an update of the field at any time, you can switch to Print preview and then back to your favorite...

Incremental Numbering
Is there is a way to number each form in publisher. I am trying to print gift certificates and woudl liek them to be numberred for contrl purposes. Canpublisher do this and if so how? thank you for you help After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Brian <BBRown7511@aol.com>... > Is there is a way to number each form in publisher. I am > trying to print gift certificates and woudl liek them to > be numberred for contrl purposes. Canpublisher do this > and if so how? You can use the Mail Merge functionality to do this. -- Ed Bennett -...

Adding 6 months to any given date
is it possible to add 6 months to a given date? i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date plus 6 months ie 04/12/03. the only way ive been able to get something near is by adding 182.5 which is half a year in days but this obviously doesnt account for the different months having varying amounts of days. Any Ideas? Regards Hoyt You could try this: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ==================================...