OfficeTips Home || VBA Section || General Section ||  Download Section  ||  Privacy Policy

Increment A Set Of Dates By X Number Of Days Quickly

Today we shall tackle a problem faced by S V Sukumar, ECSL. He writes, "I have work sheet where I keep one column as "dd/mm/yy" ( eg 10-March 2000) and if I want to change the month to April with out re-keying in what should I do? "
There are many ways to arrive at the desired output. Since I do not know whether these dates are unique or have multiple occurrences or whether the dates are in sequence. So I'll take general case.
Consider a scenario as follows:
Certain cells in Col A (namely A1,A2,A3,A4,A5) of my worksheet contain the following dates,
1-Mar-2000
2-Mar-2000
3-Mar-2000
4-Mar-2000
5-Mar-2000

<< I wish to advance these cell entries by a month quickly so that it displays >>


1-Apr-2000
2-Apr-2000
3-Apr-2000
4-Apr-2000
5-Apr-2000

Proceed as follows.
1. In any vacant cell (lets say B1) type out the number of days in the month of the cells under consideration. In our case it's March 2000 so B1 will should contain 31.
2. Select cell B1 and Click Edit, Copy from the menu.
3. Now select all the cells containing the dates (namely cells A1,A2,A3,A4,A5)
4. Select Edit, Paste Special from the menu.
5. In the Paste Special window that appears make the following choices. Under the Paste heading, select Values, under Operation heading, select Add
6. Click on OK
That's it. If you have followed the steps, the cells will display the following values:
1-Apr-2000
2-Apr-2000
3-Apr-2000
4-Apr-2000
5-Apr-2000

This technique can be used to add any number of days to a given set of dates.
 

Copyright 1999-2014 (c) Shyam Pillai. All rights reserved.