Update Timecard to new year

[Guest]

Hello,
I'd like to update a time card file to the 2008. It contains 26 worksheets,
each worksheet contains 14 dates.

For example 1/2/07 would become 1/1/08. For dates after Feb 28th, for
example 3/5/07 would become 3/3/08.

Is there a formula that would make these conversions? Sounds like I'd need
one for before Feb 28th and a second for after Feb 28th.

Thanks in advance.

Ellen

 

[Guest]

This is real easy. You don't have to worry about leap year. Just add 364 to
the date. the string on the wroksheet name has to be a valid date format
that excel recognizes. I named my worksheets 1-2-07 (you can't use slashes
in worksheet names like your example. This code will work real well.


Sub update()

For Each ws In Worksheets

WSDate = DateValue(ws.Name)
NewWSDate = WSDate + 364
NewWSName = Format(NewWSDate, "mm-dd-yy")
ws.Name = NewWSName
Next ws

End Sub

 

[Guest]

Thanks, Joel. I actually needed to change dates within the worksheet--not
the worksheet name. The dates are formatted as d-mmm.

 

[Guest]

Hi again,
I actually got it.
Enter 364 in an empty cell
Copy that cell: Edit>Copy
Now, select the range of cells you want to increase
Then do: Edit>Paste Special>add>OK
reformat cells to desired date format

Clear the 364 from the cell.

No programming necessay!!

 

[Guest]

I told you it was EASY

Hi again,
I actually got it.
Enter 364 in an empty cell
Copy that cell: Edit>Copy
Now, select the range of cells you want to increase
Then do: Edit>Paste Special>add>OK
reformat cells to desired date format

Clear the 364 from the cell.

No programming necessay!!