Free Microsoft Excel 2013 Quick Reference

Automatically Increment Dates By X Days

I have a schedule for work I use with excel and I have a date at the top.


Etc.. Until It Reaches H3 For Wednesday

The Problem Is That I Cannot Figure A Way Of Makinga Macro That When Ran Adds 7 Day To All Of Those Dates And Adjusts For Month Changes Such As Feb With
Shorter Days In The Month. All I Can Do Is Make The Macro Do A Specific Thing Like Always Revert To The Same Programmed Date Instead Of Adjusting It To The Next
Week. I Was Hoping To Make The Macro Correct The A2 Date And B3:H3 Dates.
Any Ideas?


Post your answer or comment

comments powered by Disqus
I need some helping in updating dates within Excel. I need to update the dates by seven days but then when it comes to the end of the month, I need to add only one day to start off the new month etc.

I've attached my excel file for reference. In the end, I want to add a button as I do for creating a PDF so I can update the dates with a click of a button.

Any help would be appreciated.


I have to see patients every 30 days and am using spreadsheet to keep track of it. I enter the date I see a patient in column B and in column C, I have the formula to increase the date in column B by 30 days. But I want Column C to update to the new patient seen date, when I insert new column inbetween Column B and column C for a new month, because I want to keep the date seen in Column B.

B C to B BB C

10/18/10 11/18/10 10/18/10 11/18/10 12/18/10

Thanks so much if anyone can assist me..................

Ok lets stay I have a row of dates and they are all different and I need to make all of them move forward by two days. Is there a way I can do this with out having to manually do it?

12/14/08 becomes 12/16/08
12/15/08 becomes 12/17/08
12/28/08 becomes 12/30/08


I have a column with a series of dates in.


What's the best way to increase all these dates by a fixed value (and for them to remain in column A)?

The way I have currently done it in VBA is to create a formula in column B that adds a constant to these dates and then copy the results from column B to column A, overwriting all the values in column A.

Is there a better way of doing this?


How do I sort a column filled with dates by Month, Day, then Year? The
default seems to be to sort by year first, then month.


I'm trying to show a date in MM/DD format going across five cells that represent the days of the week. I want to calculate the correct date after entering a start date. For example:

I would modify the formula in cell B6 using the DATEVALUE formula by typing in a specific start date, such as:=DATEVALUE("6/30/2008")

I would then like cells E11:I11 to show:

E11: Mon,6/30
F11: Tues,7/1
G11: Wed,7/2
H11: Thurs,7/3
I11: Fri,7/4

I chose to use the DATEVALUE function because I read that it's easier to add dates when converted into their serial number equivalent. I would also like to do this without VBA, if possible. If I'm attempting this all wrong, feel free to point me to the right direction.

Appreciate the help!


Increment Date Based on Number of Pages Printed
I have a 1 page form.
I need to have the date printed and increment forward 1 day for each page I print.
I do not want to have to manually enter in the date on each page

Is there a way to make it so that if I print 30 or 31 pages it will give me a
different date for each page?

My apologies in advance if part of this question is off topic.

How do I use vba to increment a date by one month ....

and (probably off topic)

What is the usual practise for dating 'monthly' invoices when the date of
previous invoice falls in the range 29th to 31st and the following month has
less days

Regards & TIA


I'm new around here and thought it might be nice to say hi to all before I start.

I've come here in desperation to find an answer I've been seeking for days and days. I've read my beginners VBA book, googled my rear off, but no joy.

Here's my conundrum.

I have a roster sheet for work that displays start and finish times as a date and time, and formated to hh:mm. These start / finish times are scattered throughout several worksheets within 1 workbook.

Thus, the cell displays : 08:00 or 18:00 but the data in the formula bar reads as : 20/09/2005 8:00 AM or 20/09/2005 6:00 PM

What I have been trying to do is create a macro to find all these cells containing a date and time, or even just a date, and refresh the value up by 1 day. To use the example above, I want my macro to find these particular values and change them by 1 day, resulting in 21/09/2005 8:00 AM etc etc.

Being a self taught VBA beginner, this one has proven a tad difficult for me, and was hoping I could pick up the answer and some knowledge along the way from more experienced guys in here.

I'm currently at the end of a long day and must prepare for an early start tomorrow. I'll check back here in the morning.

Thanks in advance.


Hi, I've got a userform with a listbox which contains a number of date range in the format "dd/mm/yy-dd/mm/yy". I want to take this range, and add each date within it to an array, so that I can then search through a column against this array, and check to see if the date in each row is within one of the specified ranges.

I think I'm nearly there, but I can't work out how to increment the date by one day!

Here's my code, it's failing on the line StartDte = StartDte + 1

StartDte = Left(TempWkString, InStr(TempWkString, "-") - 1)
        EndDte = Right(TempWkString, Len(TempWkString) - InStr(TempWkString, "-"))
        If IsDate(StartDte) = False Then GoTo Errhandler
        If IsDate(EndDte) = False Then GoTo Errhandler
        Do While StartDte < EndDte
            DateSearcher(iny, DateNo) = DateI
            DateNo = DateNo + 1
            StartDte = StartDte + 1
            ReDim Preserve DateSearcher(0 To DateSearchList, 0 To DateNo + 1)


Hi guys
Was hoping someone could advise a formula, UDF or VBA for cell B3 to increase date by 7 days on every new sheet?

I have two questions.
1/ I'm trying to increase the date by 7 days, by either using the fill command & the copy & paste, but it wont allow it because it insisits that the merged cells need to be identical in size. I even tried doing it by a macro but the values i get are january 1900??
2/I dont know much in fact little about excel and would loved to learn more. What do i need to learn to be able to create my own kindof formuals etc. Thank you.

l have set up a excel 2007 spreadsheet, which starts of with a variable start date then next lines are for the next day then next line the next day etc. instead of changing each date manually
is it possible to have a formula so that when l enter the start date the next days change to the following date (1 day increments)

Day 1 = 15/02/2012, Day 2 then automatically changes itself to = 16/02/2012 and so on down the line.


I intend to use a plugin for WordPress that takes data from Excel and inserts it into WP to create posts. The columns include the post, the post title and a timestamp (date)

I have at least a year's worth of posts :-) which I want to upload

I need to be able to take a starting date and go backwards for a year, simply to save me manually typing out each date.

The format of the starting date is Mar 19, 2008 16:54:11 PM

The minutes don't matter - they're just there to make the plugin work

What I want to do though is go back through the months, with each month being accurate i.e. if there are 30 days in one month, and 31 in another, that's got to be accurate.

If I try and do this manually it's going to be mind-numbing.

I've followed the instructions in Excel 2000 without any success.

Create linear and growth trends with the Series command
When you use the Series command, the resulting series replaces the original selected values. To save the original values, copy them to a different row or column. Then create the series by selecting the copied values.

Select the cell where you want to start the series. The cell must contain the first value in the series.

On the Edit menu, point to Fill, and then click Series.

Do one of the following:
To fill the series down the page, click Columns.

To fill the series across the page, click Rows.

In the Step value box, enter the value that you want to increase the series by. In a linear series, the step value is added to the first starting value and then added to each subsequent value. In a growth series, the first starting value is multiplied by the step value. The resulting product and each subsequent product is then multiplied by the step value.

Under Type, click Linear or Growth.

In the Stop value box, enter the value you want to stop the series at.
Note If there is more than one starting value in the series and you want Microsoft Excel to generate the trend, select the Trend box. In a linear series, the starting values are applied to the least-squares algorithm (y=mx+b) to generate the series. In a growth series, the starting values are applied to the exponential curve algorithm (y=b*m^x) to generate the series. In either case, the step value is ignored. The series created is equivalent to the values returned by the TREND function or the GROWTH function. I've also tried creating a starting date, and then a date one day less above it, and dragging the corner handle up the page, but all I get is a list of identical dates, even when I right click on this and try and see if there are any options.

Any help would be greatly appreciated.

Selecting rows by date for 14 day periods

I have data from a calendar in my excel spreadsheet, I want to be able to select any date located in the first Column A, and then run a macro which will find the date 2 weeks ahead, copy the data in between and paste in a new sheet or new file.

The problem is the date 2 weeks ahead must be a weekday, becasue weekend and holiday info is missing. If the date 14 days ahead is missing I want the macro to go to the previous day and try to find it, if it is also missing then go back 1 more day...until it find a date which is actually in the calendar data.

Thanks for the help, i appreciate it!


The Value in D4 which is my loan start date is 01-Dec-2008

I have the following formula in D5:


If i enter a whole number in D6, it automatically changes the year but leaves the day and month as is. I need a code that will change the month in D5 if the value entered in D6 is a decimal. Eg. entering 20 in D6 returns 01-Dec-2028 in D5. But if i change D6 to 25.50 the date in D5 changes to 01-Dec-2033. This should change to 01-Jun-2033 instead.

Can you help me with this

I use data validation to provide a list of dates. The list is a named range that includes dates.

The dates in the list change based on the date. Although the list updates automatically, the selection I have made using date validation does not. It returns an error because the data is no longer valid.

Is there a way to have the data I chose from the list update automatically when the source list changes?

I'm hoping there is a simple answer to this question:

I'm trying to set up a form that is basically add one day to a regular workday and increase the date by 2 day's if the date is on a Friday.


Cell B1= current date (input by user MO/DAY)

Cell C2= B1+1 (formatted to reflect Day of week , Month, Day, Year)

I need to add a formula that will recognize Friday's to add an additional 2 days to the formula to have the result be Monday, MO, DAY, YR

Any help?


What formula do I need to allow me to enter a date (todays) in one cell the
the next cell will give me a date x days into the future

answer via

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

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

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

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

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


kswinth's Profile:
View this thread:

What I would like to be able to do in MS Excel (2002) is this and I’m not
sure if it’s possible. How would I take a date field in Excel and decrease
that date by three BUSINESS days? I’m filling in the blanks on a large
roll-out schedule and would rather not if possible manually calculate several
hundred dates.

The first final date of completion for the project would be Monday
1/17/2005. This date appears in cell C2.

I need to show on a schedule that three business days before there will be
an installation visit. The result would be Wednesday 1/12/04. This would
appear in cell B2.

Is there a valid expression for =C2 – (3 business day?)

Using a column of Dates by day I want to determine a wk ending date (sat
date) for each date, based on comparing that date to a range of dates that
are Sun thru Sat.

I have a workbook that contains 14 sheets. I have a sheet for each month
followed by 2 sheets for information.

Each Month sheet has the following column headings associated from columns A
through J:-

Owner; from date; number of days; to date, address, ID, month, input by;
date; time.

I have to input data in columns A, B, C E, H, I and J.

Columns A and H are pick lists.

I have formulas in the following columns:-

Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)

Column F: =IF(ISBLANK(Enn),"",+Fnn+1)

I want column I to be populated AUTOMATICALLY (do not want to use the
Control and semi-colon etc ) with the current date (dd mmm yy format) and
column J to be populated with the current time (format hh:mm am/pm) only when
column H is not blank.

Once the date and time have been entered in columns I and J, I do not want
it to be updated with a new time the next time someone goes into the work
book or when the date changes the following day. It should only be populated
to ‘blanks’ is there is no data in column H

Additionally, I do not have any experience of creating macro’s or VBA and
therefore the information given needs to be plain!!

Any help greatly appreciated.

I have a table of records created to be used by pivot table. Once I created the first record, I like to duplicate this record for each month until an end month and year I specify. Each record has a date field. I like the macro to duplicate or copy/insert a new record and then proceed to change this date field in the new record by incrementing it by 1 month. Repeat this until a end date specify by me .

I have tried using record macro and succeed up to copy and insert but am stump with automating the increment date codes I need to automate this.

Any sample or pointers will be most appreciated.

No luck finding an answer? You could always try Google.