# Julian Date to Leap and Perpetual

#### Mandalorean2356

##### New Member
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*

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Dave Patton

##### Well-known Member
Welcome to the forum.

- 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
ABCDE
46-Jun-2121157Current date to ~ Julian
5211576-Jun-21Text to Date
6211576-Jun-21Number to date
7
1a
Cell Formulas
RangeFormula
B4B4=TEXT(A4,"yy")&A4-DATE(YEAR(A4)-1,12,31)
B5:B6B5=DATE("20"&LEFT(A5,2),1,RIGHT(A5,3))

Cell Formulas
RangeFormula
B9:B12B9=TEXT(A9,"yy")&TEXT(A9-DATE(YEAR(A9)-1,12,31),"000")
C9:C12C9=DATE("20"&LEFT(B9,2),1,RIGHT(B9,3))
A10:A12A10=A9+1

Last edited:

Replies
5
Views
1K
Replies
1
Views
548
Replies
1
Views
146
Replies
1
Views
140
Replies
12
Views
678

1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

### 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.

### Which adblocker are you using?

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

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