Adding a year onto all dates in a spreadsheet

jocstoke

New Member
Joined
Jun 19, 2019
Messages
3
I have a large group of spreadsheets, all with multiple tabs and values, of which approximately 10% of the cells contain dates. Once a year I have to manually go through these sheets and add a year onto all of the dates. For example, a cell containing 01/07/1935 would be changed to 01/07/1936. This process currently takes a couple of days. Is there any way this can be done automatically, by running a function or something, so that the dates are found and updated for me? Or even something that just lists all the cells that contain dates so I don't have to trawl through hundreds of tabs manually looking for the cells that contain dates? Any help would be much appreciated.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
Try this on a copy of one spreadsheet and see if does as required.

Code:
For Each sh In ThisWorkbook.Worksheets
    For Each c In sh.Cells.SpecialCells(xlCellTypeConstants)
        If IsDate(c.Value) Then
            c.Value = WorksheetFunction.EDate(c.Value, 12)
        End If
    Next
Next
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,068
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Are all the dates in the same column on each sheet? Can you provide a sample of what your sheets look like? Are there multiple workbooks or only one? If multiple workbooks, are they all located in the same sub-directory? Will all dates in your files be amended to add just one year?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,185
Office Version
  1. 2016
Platform
  1. Windows
Try this on a copy of one spreadsheet and see if does as required.

Code:
For Each sh In ThisWorkbook.Worksheets
    For Each c In sh.Cells.SpecialCells(xlCellTypeConstants)
        If IsDate(c.Value) Then
            c.Value = WorksheetFunction.EDate(c.Value, 12)
        End If
    Next
Next
Can restrict the number of cells to loop :
Code:
For Each c In sh.Cells.SpecialCells(xlCellTypeConstants[COLOR=#ff0000], 1[/COLOR])
 

jocstoke

New Member
Joined
Jun 19, 2019
Messages
3

ADVERTISEMENT

Are all the dates in the same column on each sheet? Can you provide a sample of what your sheets look like? Are there multiple workbooks or only one? If multiple workbooks, are they all located in the same sub-directory? Will all dates in your files be amended to add just one year?

Thanks for your reply! Answers below.

Are all the dates in the same column on each sheet? No
Can you provide a sample of what your sheets look like? I'd be happy to but not sure how
Are there multiple workbooks or only one? Multiple
If multiple workbooks, are they all located in the same sub-directory? Yes
Will all dates in your files be amended to add just one year? Yes
 

jocstoke

New Member
Joined
Jun 19, 2019
Messages
3
Try this on a copy of one spreadsheet and see if does as required.

Code:
For Each sh In ThisWorkbook.Worksheets
    For Each c In sh.Cells.SpecialCells(xlCellTypeConstants)
        If IsDate(c.Value) Then
            c.Value = WorksheetFunction.EDate(c.Value, 12)
        End If
    Next
Next

Sorry if I'm being dense but I'm not sure how to apply this code.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Make a copy of your workbook. Open the copy and press ALT-F11. Press insert then module. In the 'white space' paste the below. Ive added a bit of error handling and footoo's suggestion:

Code:
Sub AlterDate()

Dim sh As Worksheet, rng As Range, c As Range

For Each sh In ThisWorkbook.Worksheets
    On Error Resume Next
    Set rng = sh.Cells.SpecialCells(xlCellTypeConstants, 1)
    If Not rng Is Nothing Then
        For Each c In rng
            If IsDate(c.Value) Then
                c.Value = WorksheetFunction.EDate(c.Value, 12)
            End If
        Next
    End If
    Set rng = Nothing
Next
        
End Sub

Now close the editor. The highest cross top right. In excel press view then macros. Click 'AlterDate' then run. Check dates have changed.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Sorry if I'm being dense but I'm not sure how to apply this code.

let me try

1) Menu > Developer > Visual Basic

<img src="https://www.pixelsbin.com/images/2019/06/19/11ed79019d22a0792.jpg" alt="11ed79019d22a0792.jpg" border="0">

2) Menu > Insert > Procedure

<img src="https://www.pixelsbin.com/images/2019/06/19/29e458fc4c7422315.jpg" alt="29e458fc4c7422315.jpg" border="0">

3) Input a name > enter

<img src="https://www.pixelsbin.com/images/2019/06/19/339231f53bc91e42b.jpg" alt="339231f53bc91e42b.jpg" border="0">

4) copy and paste Steve's code post#2 inbetween the Public Sub and End Sub, > OK

<img src="https://www.pixelsbin.com/images/2019/06/19/4afb9665f742ff0d1.jpg" alt="4afb9665f742ff0d1.jpg" border="0">
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
5) back to the spreadsheet, Menu > Developer > Macros > select the only macro and run

<img src="https://www.pixelsbin.com/images/2019/06/19/56b7eb1b9dd86930d.jpg" alt="56b7eb1b9dd86930d.jpg" border="0">
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top