There is no 100th day in January, so DATE simple moves forward 100 days from January 1 and figures returns the correct date.
The formula on this page takes advantage of this behavior. The year assumed to be 2015 in this case, so 2015 is hard-coded for year, and 1 is used for month. The day value comes from column B, and the DATE function calculates the date as explained above.
Extracting a year value from a Julian date
If you have a date in a Julian format, for example, 10015, where the format is "dddyy", you can adapt the formula as follows:
If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions. Background "Julian date format" refers to a format where the year value of a date is combined with the...
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.