Convert Julian dates to the Gregorian calendar

[Livin404]

Greetings, I managed to get the proper five digit Julian Dates for Columns E & G both beginning on Row 2. I know the formula

=DATE(IF(0+(LEFT(E2,2))<30,2000,1900)+LEFT(E2,2),1,RIGHT(E2,3))

does work, but I'm going to need the days difference, which I already have, but I just need the conversion and the Calendar dates displayed to make it more understandable for the reader.

Thank you,

 

[Livin404]

some research, if I include

Function aDate(jDate As String)
NormDateYear = CInt(Mid(jDate, 1, 2))
If NormDateYear < 30 Then
NormDateYear = NormDateYear + 2000
Else
NormDateYear = NormDateYear + 1900
End If
NormDateDay = CInt(Mid(jDate, 3, 3))
aDate = DateSerial(NormDateYear, _
1, NormDateDay)
End Function

In my module will that convert for me though I didn’t specify the columns. I’m away from my computer now. Thank you.

 

[Bo_Ry]

Please try

Book1.xlsx
	E	F	G	H	I	J
2	20124	1	20064		60	60
3	21060	1	20355		71	71
4	21023	1	20337		52	52
5	21015	1	20342		39	39
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=DATE((19+(LEFT(E2)>"3"))&LEFT(E2,2),1,RIGHT(E2,3))-DATE((19+(LEFT(G2)>"3"))&LEFT(G2,2),1,RIGHT(G2,3))
J2:J5	J2	=MMULT(DATE((20-(LEFT(E2:G2)>"3"))&LEFT(E2:G2,2),1,RIGHT(E2:G2,3)),{1;0;-1})

 

[Livin404]

Please try

Book1.xlsx
	E	F	G	H	I	J
2	20124	1	20064		60	60
3	21060	1	20355		71	71
4	21023	1	20337		52	52
5	21015	1	20342		39	39
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=DATE((19+(LEFT(E2)>"3"))&LEFT(E2,2),1,RIGHT(E2,3))-DATE((19+(LEFT(G2)>"3"))&LEFT(G2,2),1,RIGHT(G2,3))
J2:J5	J2	=MMULT(DATE((20-(LEFT(E2:G2)>"3"))&LEFT(E2:G2,2),1,RIGHT(E2:G2,3)),{1;0;-1})

That looks great; however, if I can get that to a VBA would be brilliant? This thing will be updated regularly and the user will have no idea about these formulas. This is only a small part of a rather larger advanced filter VBA. This workbook will be cleared after each time the macro is run. However, at least I know that is not necessary to covert first. Thank you.

 

[Bo_Ry]

Try

Sub DifJdate()
Dim rn As Range
    Set rn = Range("E2", Cells(Rows.Count, "E").End(xlUp))
    rn.Offset(, 4).Value = Evaluate(Replace("MMULT(DATE((20-(LEFT(#)>""3""))&LEFT(#,2),1,RIGHT(#,3)),{1;0;-1})", "#", rn.Resize(, 3).Address))
End Sub

 

[Livin404]

Try

Sub DifJdate()
Dim rn As Range
    Set rn = Range("E2", Cells(Rows.Count, "E").End(xlUp))
    rn.Offset(, 4).Value = Evaluate(Replace("MMULT(DATE((20-(LEFT(#)>""3""))&LEFT(#,2),1,RIGHT(#,3)),{1;0;-1})", "#", rn.Resize(, 3).Address))
End Sub

Beautiful job thank you. Exactly what I needed. In fact better spared me a couple macros.