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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
I am not 100% clear on your setup, but since you are using a macro to calculate your values, this code line will output the year as a two digit number followed by the day of the year as a three digit number for the day that the code is executed (thus producing the 5-digit number you are seeking)...

VariableOrCellReference = Format(Now, "yy") & Format(Format(Now, "y"), "000")
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am not 100% clear on your setup, but since you are using a macro to calculate your values, this code line will output the year as a two digit number followed by the day of the year as a three digit number for the day that the code is executed (thus producing the 5-digit number you are seeking)...

VariableOrCellReference = Format(Now, "yy") & Format(Format(Now, "y"), "000")
Thank you, I’m looking forward to trying it when I get home. I take it I can have it in one macro for Columns G & I starting at row 2 and going now until there is a blank cell? I would like to avoid two macros if I can.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Again, I am not 100% sure of your setup nor what you are trying to do with it. With that said, the code to the right of the equal sign will return the number you seek for the current day it is executed on... you can assign that to whatever cell you want. Since that assignment is a calculated value, it will not change once assigned. So, if you execute the code today (December 20, 2020), it will return the number 20355... if you then execute the code tomorrow, it will return 20356... and so on.
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Again, I am not 100% sure of your setup nor what you are trying to do with it. With that said, the code to the right of the equal sign will return the number you seek for the current day it is executed on... you can assign that to whatever cell you want. Since that assignment is a calculated value, it will not change once assigned. So, if you execute the code today (December 20, 2020), it will return the number 20355... if you then execute the code tomorrow, it will return 20356... and so on.
Thank you, the Columns are actually in E & G. Once I get the full Julián Date, I will convert to a short Calendar Date where I will subtract the date of Column G from Column E. The goal in the end is to identify those dates more than 60 days. Please understand I had to create macros from a data source I got these from just to get to where I am now. The last one is the one I'm trying to work on which has your input.

Julian Date.JPG



VBA Code:
Sub Convert_Text_format()
Columns("E:E").NumberFormat = "@"
Columns("G:G").NumberFormat = "@"
End Sub

VBA Code:
Sub Three_day()
  Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("G2:G" & LastRow)
        If InStr(rng, "/") > 0 Then
            rng = Mid(rng, 1, WorksheetFunction.Find("/", rng) - 1)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

VBA Code:
Sub Last_three_Juliandate()
With Range("E2", Range("E" & Rows.Count).End(xlUp))
x = .Address
.Value = Evaluate("if(len(" & x & ")=2,""0""&" & x & "," & x & ")")
.Value = Evaluate("if(len(" & x & ")=1,""00""&" & x & "," & x & ")")
End With
End Sub

VBA Code:
Sub Full_Julian_Date()
With Range("G2", Range("G" & Rows.Count).End(xlUp))
VariableOrCellReference = Format(Now, "yy") & Format(Format(Now, "y"), "000")
End With
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Replace my example variable that I named VariableOrCellReference with .Value (make sure to include the leading dot).
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Replace my example variable that I named VariableOrCellReference with .Value (make sure to include the leading dot).
Thank you for getting back, and I plugged in your formula, and am pleased to say it is some progress now. The first part inserted the 20 because of the "now" but the second part put in the current three number code for the day from start to finish. I'm looking to just have the 20 placed in front of the three numbers that are already there. I definitely need to have the columns change to the date format. In about 10 days the number placed in front will be 21. This also means after the first when I cut and paste and run the macro all dates prior to 366 (this being a leap year) will still need to have 20 in front.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
Platform
  1. Windows
Oh, I thought you wanted code to generate the whole 5-digit number for you. Okay, I think I see what you are after, give this a try...
VBA Code:
Sub Full_Julian_Date()
  With Range("G2", Range("G" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("IF(LEN(@)=3,TEXT(NOW(),""yy"")&@,@)", "@", .Address))
  End With
End Sub
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Oh, I thought you wanted code to generate the whole 5-digit number for you. Okay, I think I see what you are after, give this a try...
VBA Code:
Sub Full_Julian_Date()
  With Range("G2", Range("G" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("IF(LEN(@)=3,TEXT(NOW(),""yy"")&@,@)", "@", .Address))
  End With
End Sub
Thank you. It works great. Next year when I run this will it know what dates to add 20 in front or 21? These are registration and expiration dates and it tracks the number days in the system.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,884
Office Version
  1. 2010
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

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