Join Dennis Taylor for an in-depth discussion in this video Convert to and from Julian dates, part of Excel 2016: Cleaning Up Your Data.
- [Instructor] In this worksheet called JulianDates,…we see a series of those kinds of dates,…Julian Dates, in Column A.…If you're not familiar with the term,…the blue panel here at the bottom…of the worksheet describes it.…A Julian Date is a combination of the current year…and the number of days since the beginning of the year.…So the first day of the year, for example, 2017,…would be 2017 and then January first, 001.…If it's the last day of the previous year,…that was a leap year, 2016366…represents December 31st of 2016.…
Now, at different times you might encounter these,…and you might want to convert those dates…into a date like we see here in cell B2.…That's the representation of the date…that we see to the left.…And over in Column D we've got the reverse situation,…where we're converting a date into a Julian Date.…So let's focus over in Column B first,…I'll zoom in a bit.…We need to, in effect, write a…formula that constructs a date.…And the function we will be using is the DATE function…that's depicted in cell C1.…
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Moving and inserting rows and columns of data with a simple drag
- Replacing data at the character level
- Converting dates with text functions
- Converting text data to values
- Using the CONCAT and TEXTJOIN functions to combine data
- Splitting data into columns via the Text to Columns feature
- Using Flash Fill for faster combining and splitting
- Checking and correcting spelling mistakes