[b] Replace part of a date[/b] 1

[trystanhuwwilliams1]

Hi there, not sure if this can be done - but I've inherited a database (very old Dataflex) which is unfortunately still live. I've managed to set up a facility which exports the records to EXCEL. Once in EXCEL I need to perform some data conversion before I eventually export it to Access.

To get around the Year 2000 issue the data entry staff decided to put 2020 instead of 2000. I now want EXCEL to change these dates back to 2000.

Does anyone know how to do this in code?

Thanks,

Trystan

 

[jonsi]

Try using an IF statement into an adjacent column.

This one assumes your date is in F1.
=if(f1>=43831,(f1-7305),f1)

43831 = 1st Jan 2020
7305 is difference between 1st Jan 2000 & 1st Jan 2020

then use copy>Paste Special>Values to overwrite the original dates

hwyl
Jonsi

 

[TonyJollans]

Hi trystanhuwwilliams1,

You should really ask code questions in Forum707, but as you're here, try this:

[blue]For Each objCell In ActiveSheet.UsedRange
    If IsDate(objCell) Then
        If Year(objCell) = 2020 Then
            objCell.Value = DateSerial(2000, Month(objCell), Day(objCell))
        End If
    End If
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

Trystan,

Since this is the Office Forum, if your old date is in A1
[tt]
=Date(2000,Month(A1),Day(A1))
[/tt]
which is essentially Tony's solution in spreadsheet solution format, except it's in a new column.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]