'Partial' Julian Date Issue

[Glaswegian]

Hi

I have data from our systems that is causing me some problems.

The date column appears to be behaving as if it is a Julian date - but I don't think it is really a Julian date.

For example, the date format is dd-mmm-yy - 31-Dec-29.

As long as the date year is less than 30 then Excel sees it as the correct century - in the example above Excel sees the year as 2029 - which is correct. However, if the year value is 30 or more, Excel sees it as 20th Century. For example, 31-Mar-30 is interpreted as the year 1930 when it should be 2030.

This is Julian date behaviour to me but I can't understand it and I cannot work out how to convert the dates to a standard format that shows correctly.

Thanks for any help.

[GarysStudent]

Give this a try:

Sub YearFixer()
Dim D As Date
Dim r As Range
For Each r In Selection
D = r.Value
If Year(D) < 2000 Then
r.Value = DateSerial(Year(D) + 100, Month(D), Day(D))
End If
Next
End Sub

[p45cal]

you may find this useful:http://office.microsoft.com/en-gb/excel-help/change-the-date-system-format-or-two-digit-year-interpretation-HP010342240.aspx?CTT=1#_Toc298144367

extract:
Change the way two-digit years are interpreted

Important To ensure that year values are interpreted as you intended, type year values as four digits (for example, 2001, not 01). By entering four-digit years, Excel won't interpret the century for you.
If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows:

00 through 29 is interpreted as the years 2000 through 2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.
30 through 99 is interpreted as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998. In Microsoft Windows, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed.
8894

[Glaswegian]

Thanks Gary - works nicely!

I did review that p45cal but I the data from our systems is rubbish....

[GarysStudent]

You are quite welcome.