Immediate window for changing dates

[Eddie_th]

Hello!

I am implementing a code for the immediate window because I have a costumer who needs to copy and paste dates. But when he does, the dates are modified by 4 years and 1 day. Therefore I want to show him how he can get rid of this.

My idea was to select the cells containing the wrong dates and then use the immediate window. The problem is when we select cells which do not contain dates. It may sound rendundant, but it is not. For example, if we have several cells without dates between our cells with dates, something like this:

01-01-2016
01-01-2016
01-01-2016
Absent
Absent
01-01-2016
Absent
01-01-2016
.
.
.

It is easier to just select all the column instead of using the CTRL+Click method. Therefore, I was using the following code in the immediate window but I get a Next without For error:

For Each cell In Selection:if IsNumeric(cell.Value)=True then cell.Value=cells.Value+1462:Next cell

[Pawel]

Try this:

Sub ABCD()
Dim cell As Range


For Each cell In Selection: If IsNumeric(cell.Value) = True Then cell.Value = cell.Value + 1462:
Next cell


End Sub

[Pawel]

Or actually this should do the job:

Sub adddate()
Dim cell As Range
Dim r As Range
Set r = Selection
If IsDate(r) Then
For Each cell In r
Selection.Value = DateAdd("d", 1462, CDate(r))
Next cell
End If
End Sub

Let me know if that helps.

[gmaxey]

Pawel,

I am by no mean an Excel expert, barely a dabbler, but I couldn't get your method to work. This worked when run within a module:

Public Sub ModifyDateString()
Dim cell As Range
  For Each cell In Selection.Cells
    If IsDate(cell.Value) Then
      cell.Value = DateAdd("d", 1462, CDate(cell.Value))
    End If
  Next
End Sub

... but it looks like the OP wants to run the code from the immediate window.

[Pawel]

oohh... Now I'm embarrassed, I missed that info. Don't know why, since it was mentioned like 5 times before. Sorry for trouble.

[Paul_Hossler]

Since Excel stores dates as a Double and 'Days From', and you're off by 4 years, you might check that the workbooks are both using the same 'Days From' base

Capture.JPG

I've never had to use the 1904 base, but I understand it's there for compatibility with something called Lotus-123

[gmaxey]

No trouble. Actually I was hoping you would recover then go on to tell me how to call ModifyDateString using the Immediate Window. Too much a numb and too embarrassed to ask

[Eddie_th]

Since Excel stores dates as a Double and 'Days From', and you're off by 4 years, you might check that the workbooks are both using the same 'Days From' base

Capture.JPG

I've never had to use the 1904 base, but I understand it's there for compatibility with something called Lotus-123

Yes, thank you. But the problem we have is that there are other dates in other worksheets that will be also modified and we only want the dates that had been pasted to be modified.

Maybe I will just use the normal VBA instead of using the Immediate Window, I will just have to explain that to this person.

Thank you guys for your time, I really appreciate it!