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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
EFGHI
1DTExpiresSTSDTE/EnteredCNTRY/STANDINGDAYS IN REGISTRY
20081314KUW60
30081314USA60
40461352KOR15
50461352USA15
60201326GER60
70201326JPN60
80501356KOR60
90501356PHI60
100501356THA60
110232329GBN59
120232329GER59
130182324HOD59
140182324ITA59
150182324SPA59
Database
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,


VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

date.JPG
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,130
Messages
5,640,300
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top