Adding a year onto all dates in a spreadsheet

[jocstoke]

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.

 

[steve the fish]

Try this on a copy of one spreadsheet and see if does as required.

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]

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]

Try this on a copy of one spreadsheet and see if does as required.

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 :

For Each c In sh.Cells.SpecialCells(xlCellTypeConstants[COLOR=#ff0000], 1[/COLOR])

 

[jocstoke]

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]

Try this on a copy of one spreadsheet and see if does as required.

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]

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:

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]

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]

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">