Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90746 Viewers
80 Video lessons · 137994 Viewers
59 Video lessons · 56787 Viewers
52 Video lessons · 70407 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.