Julian Date to Leap and Perpetual

[Mandalorean2356]

Any help would be great. I'm in the military and we use Julian dates to track alot of inventory. Each year we need audit our items and we do this by checking the Julian dates adding its shelf life by months and getting its due date. This is all done on scratch paper. I'm trying to create an Excel Sheet where I enter in the needed information. I enter in the Julian date add the months and get a calendar year in my total. I have managed to get a conversion from Julian to calendar in Excel using =("1/1/"&(IF(LEFT(A6,2)*1<20,20000,1900)+LEFT(A6,2)))+MOD(A6,1000)-1. My biggest hurddle is having Excel give the proper day. Every total comes out in a leap year and nothing in perpetual (non-leap years), It would be great if the formula knew when to give a leap year and a perpetual year. if anyone knows any formula which might help that would be great. *First Post*

 

[Dave Patton]

Welcome to the forum.

Please provide some additional information.
- how are you defining Julian date?
- provide a few examples complete with expected results
- how is the Julian date created?
- what range of dates are you considering?

Some agencies apparently use the following:

T202106a.xlsm
	A	B	C	D	E
4	6-Jun-21	21157	Current date to ~ Julian
5	21157	6-Jun-21	Text to Date
6	21157	6-Jun-21	Number to date
7
1a

Cell Formulas
Range	Formula
B4	B4	=TEXT(A4,"yy")&A4-DATE(YEAR(A4)-1,12,31)
B5:B6	B5	=DATE("20"&LEFT(A5,2),1,RIGHT(A5,3))

T202106a.xlsm
	A	B	C
9	27-Feb-12	12058	27-Feb-12
10	28-Feb-12	12059	28-Feb-12
11	29-Feb-12	12060	29-Feb-12
12	1-Mar-12	12061	1-Mar-12
13
1a

Cell Formulas
Range	Formula
B9:B12	B9	=TEXT(A9,"yy")&TEXT(A9-DATE(YEAR(A9)-1,12,31),"000")
C9:C12	C9	=DATE("20"&LEFT(B9,2),1,RIGHT(B9,3))
A10:A12	A10	=A9+1