Add the two character in front of the 3 day character Julian Date

[Livin404]

Greetings, I have two columns that I managed to setup (in text format) and isolate the last three days of the Julian Date, Columns (G2 & I2). What I would love to have is 20 or 21 placed in front. This be based on the current date. So today’s date will be 20355. However going forward the macro will have to be smart enough to change to 21001 at the beginning of the year. The important thing is that when this happens I can have dates from the previous year keep their 20XXX status. These partial dates are cut and pasted from a database. This will be done on a weekly bases, so new dates as well as previous dates will be run through the macro again. The end goal is once this is done I’ll get the difference between the two dates when Column G will be subtracted from I. The time does not need to be included. For now I’m only concerned about the two digit numbers, the other part I included for context.

 

[Livin404]

It will automatically use the 2-digit year for the year the code is executed in. So, up to and including December 31st of this year, it will use 20 when the code is executed... from January 1st to December 31st of next year, it will use 21 when the code is executed... the year after that it will use 22 when executed and so on.

Thank you, I was afraid of that. I created a great advanced filter to detect 60 days or greater when G is subtracted from E. I guess I'll have to wait about 60 days into the new year before I can use it. In the meantime I'll manually have to change the years for some of them. Thanks again and happy holidays.

 

[Rick Rothstein]

If I understand you correctly, you need either a delay or an advancement on when the year value switches (for the current time frame) from 20 to 21. If you explain in detail about this, perhaps I can modify the code to implement it... I just need details (remember, I know nothing about your actual data layout, how you want to interact with it nor what results you actually want for it).

 

[Livin404]

If I understand you correctly, you need either a delay or an advancement on when the year value switches (for the current time frame) from 20 to 21. If you explain in detail about this, perhaps I can modify the code to implement it... I just need details (remember, I know nothing about your actual data layout, how you want to interact with it nor what results you actually want for it).

Thank you for getting back to me. I've attached a XL2BB to this response, and I hope it provides a more clarity. I will subtract Columns G from E. The good news is that the data system the dates are sourced from will not allow any dates earlier than the current date to appear for Column E, and with the original VBA you provided, it will work wonderful. It is Column G that I have concern with. Simply put I cannot have any negative numbers when I subtract Column G from E. Column I cannot have any negative numbers. Column E will "ALWAYS" need to have the higher number. Because Column E will not allow to have any dates earlier than the current date your VBA is perfect for that. We need to have the for two digits of Column E be based on what the number is Column G is. The last three digits will never be changed in either Column.

21 Jan.xlsm
	E	F	G	H	I
1	DTExpires	STS	DTE/Entered	CNTRY/STANDING	DAYS IN REGISTRY
2	008	1	314	KUW	60
3	008	1	314	USA	60
4	046	1	352	KOR	15
5	046	1	352	USA	15
6	020	1	326	GER	60
7	020	1	326	JPN	60
8	050	1	356	KOR	60
9	050	1	356	PHI	60
10	050	1	356	THA	60
11	023	2	329	GBN	59
12	023	2	329	GER	59
13	018	2	324	HOD	59
14	018	2	324	ITA	59
15	018	2	324	SPA	59
Database

 

[Livin404]

If I understand you correctly, you need either a delay or an advancement on when the year value switches (for the current time frame) from 20 to 21. If you explain in detail about this, perhaps I can modify the code to implement it... I just need details (remember, I know nothing about your actual data layout, how you want to interact with it nor what results you actually want for it).

I think perhaps I’m making this more difficult than necessary. Since I’m dealing with Julian Years and there will at most be just one separating the columns whether it be 21 & 20 or 22 & 21, and so on. The result will always be the same at least I think.
So I something like may work. Thank you,

Sub Full_Julian_Date()
  Dim E As Range, G As Range
  For Each G In Range("G2", Range("G" & Rows.Count).End(xlUp))
    Set E = Range("E" & G.Row)
    If G > E Then
      E = CDbl("21" & E)
      G = CDbl("20" & G)
    Else
      E = CDbl("20" & E)
      G = CDbl("20" & G)
    End If
  Next
End Sub

 

[Rick Rothstein]

Are your Columns E and G formatted as General with a custom format of "000" or are they formatted as Text and your values are physically 3 characters long?

A second question... how does the 60 days you mentioned earlier fit in with the code you posted?

 

[Livin404]

Are your Columns E and G formatted as General with a custom format of "000" or are they formatted as Text and your values are physically 3 characters long?

A second question... how does the 60 days you mentioned earlier fit in with the code you posted?

Those columns are in text format. I ran your macro again and it is inserting 21 now in front, so that is working fine. I tried the alternative macro it does work because the columns are in a text format it will read 017 as a higher number than 355. It is so frustrating. I changed those Columns into a general format after I used your macro. I thought I could change the alternate macro to make changes if necessary so Column E will be greater than Column G. Now I would need a macro to change the 21 to 20 in Column G. The 60 days is just a number I'm using with advanced filter for days over 60. Thank you.

 

[Livin404]

Are your Columns E and G formatted as General with a custom format of "000" or are they formatted as Text and your values are physically 3 characters long?

A second question... how does the 60 days you mentioned earlier fit in with the code you posted?

Hello sir, I thought I let you know I think it's all sorted out now and it runs properly. You have always been a great help thank you so much! The following is a VBA I think works, I'm not seeing any problems after I tested it. Thank you,

Sub Full_Julian_Date()
  Dim E As Range, G As Range
  For Each G In Range("G2", Range("G" & Rows.Count).End(xlUp))
            Set E = Range("E" & G.Row)
             If CInt(G) > CInt(E) Then  
                    E = CDbl("21" & E)
                    G = CDbl("20" & G)
            Else
                    E = CDbl("20" & E)
                    G = CDbl("20" & G)
            End If
  Next
     Columns("E:E").NumberFormat = "General"
    Columns("G:G").NumberFormat = "General"
End Sub