Julian Date to Leap and Perpetual

Mandalorean2356

New Member
Joined
Jun 6, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
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:
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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
Back
Top