Join Dennis Taylor for an in-depth discussion in this video Working with dates and times, part of Excel Essential Training (Office 365).
- [Instructor] Excel has a great deal of computational capability when you work with dates and times. On this worksheet called Dates-Times in the workbook zero two, Entering Data, there's a date in cell B two. It's got slashes in it and refers to August 10th 2018. In different cultures you can display these differently so that the day is first. Now, anytime you're entering dates, you want to use slash or dash. If I type this same entry with a dash, eight dash 10 dash 18 Enter, I'll get the same display.
Anytime you use slash or dash, Excel evaluates that information as if it could be a date, and if it is, it aligns it on the right hand side of the cell. If you type a date that doesn't exist, for example November 31st, I'll even type in the four digit year here, when I press Enter, Excel does something a little different. But the difference here is so subtle you wouldn't notice it. I do recommend in any column that has a date from time to time, particularly during the data entry phase, keep that column a bit wider. I'm gonna drag the boundary between B and C to the right and that pops out.
That is not a date. And, in fact, Excel is saying that's just a bunch of characters. Behind the scenes, and you don't need to know too much about this, but every single date has a separate value starting in January 1st 1900. And that means when you're entering this, this is actually a value. That's partially the reason this is right-aligned. And because it is a value, you can write formulas that refer to this. Or if you have two different dates you can subtract them to calculate the difference. I think you can recognize how important that is in banking, and finance, and all kinds of different endeavors.
If I write a formula right here, and I know some of you haven't yet worked with formulas, I'm gonna type equal B two, that's this cell, plus 100. I'm going to be getting a date that's 100 days after August 10th. And there it is, November 18th. That's 100 days later. The formula adds 100 to it. And I just as easily could have subtracted that 100 also. When you work with times, enter them with colons. Now you may choose to type a.m./p.m. If you're more familiar and prefer that style, you would type this same entry here as four colon four three space P.
And of course if we're a.m., space A and so on. So you can use either style and you can adjust them or switch when necessary as well, too. And here, too, behind the scenes you actually have entered a value. It's a fractional part of the day. So if you were to type in, for example, 6:00 p.m., that's 3/4 of the day. The number .75 is being stored in the background. Maybe a little bit of trivia there, but still. As we enter these and we do them in a standard way using colons for time and using slash or dash with date, we will be able to work with these times and dates in a productive way.
And on the Formulas tab in the ribbon, there are a whole bunch of functions related to date and time. We can calculate working days, networking days, not counting holidays. All kinds of features here related to dates and times because it's a vital ingredient in many an Excel worksheet.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.