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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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?
 
Upvote 0
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])
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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">
 
Upvote 0
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">
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top