Join Dennis Taylor for an in-depth discussion in this video Converting to or from Julian dates, part of Cleaning Up Your Excel 2010 Data.
In some environments, you might encounter what are called Julian dates, and they…exist in one of two forms.…In column A, we see some entries, and in cell A2, the first four characters that…are on the left, 1990, represent a year. The last three characters represent the…day of the year, the 31st day of the year.…Second example, that's in the year 2004, and the day is the 211th day, and we see…some other examples here.…So what we need to do, in effect, is turn that into an Excel date that we can work with.…
In column D is a variation on Julian dates that is shorter, and the first two…characters that represent the year.…There is a parallel in column D with column A--same dates actually,…but different form.…So here it's 90, the year 1990, and then 031.…So the way we handle these is to essentially start with the Date function, which…you might not have used.…It's pretty straightforward. It allows us to construct an Excel date out of…existing information.…It would be a little bit tricky here and we won't actually use these terms, but…
- Moving or inserting rows and columns of data with a simple drag
- Using Text to Columns
- Harnessing the Find and Replace command to replace data at the character level
- Dealing with special characters and wildcards during search
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.