How to Work with Dates and Times in Excel 2013

show more Working with dates and times provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Working with dates and times

In this worksheet called "Dates-Times" in the 02-Entering Data Workbook, we've got a date entry in column B and some time entries in column D. Excel is adept at handling date and time information. You want to start off by just making sure that when you do put dates into Excel that you want to enter them in just a few different ways. First of all, if you're working in the United States with Excel, your standard Excel settings are to display dates as from left to right, month/day/year. In other countries it's likely to be day/ month/year, but this represents January 8, 2013. It's typed with slashes. The standard way of entering dates in Excel is with slashes or hyphens. No matter how you type it, you will see the display as slashes. So if we want to put in for example, November 17, 11/17 and it's in the same year, we'll type 13, Enter, no great surprises there. Same thing would happen of course if we type hyphens. It will display with the slashes. A small tip here, any date in the first portion of this century--the first 10 years--if for example, it's April 5, if it happens to be 2007, just type 7, Excel displays the entry this way. One oddity that you might or might not run into--if you're dealing with dates within a certain timeframe--maybe you keep track of retirees or maybe you're keeping track of the age of buildings or something, someone who's born December 13 in 1930, you'd probably type it that way and press Enter and not at all be surprised. How about someone born the year before, on November 12 of 1929? You'd probably type it that way and then be real surprised when you see this kind of a display. Here's what Microsoft has done and they will change this in years to come. Any date entry that has a year from 30 through 99 is automatically considered last century, and any date entry that has the year from 0 through 29 is automatically this century. Now, when you're typing these entries, if you're dealing with data, just play it safe and type four-digit years. In this case, we would be typing--of course we can edit it now--but if we were just typing this, it's 11/12/1929--if that's what you mean--because we just put in 29 as we saw earlier, it's going to be 2029. Again, the reason for putting these in, in these ways is that date entries are actually values. They're stored as numbers, that's why they're right aligned. If you do this correctly, you open the door for extensive use of these. We can find out day of the week here. We can find out amount of time elapsed between different dates. There's just a variety of things we can do using Excel date functions that are built-in. Similarly with times, make your time entries in 24-hour style or an A.M/P.M. style and use colons. Recognize that although that's not right or wrong it's 1:32 A.M--unless you type A.M. or P .M.--it assumes A.M. So an entry here-- and we can type these in a variety of ways-- use colons, if it's 4:32 p.m., you can even type it "4 colon 3, 2 space P", and it gets stored that way. Also with times, by putting in times correctly, we can later calculate time differences or time into the future. The Excel DateTime system is designed to work by the way until the year 10,000. We're in good shape here if we put in our dates and times correctly from the beginning.

Working with dates and times
Video duration: 3m 32s 6h 32m Appropriate for all


Working with dates and times provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...