Introduction

Welcome! Dates, Times & Formatting Tips

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Excel Expert Level 2. In this course we will focus on how Microsoft Excel handles dates and times, including how they are stored internally, using custom date and time codes, and working with popular date and time functions. We will discuss customizing date formats, calculating differences between dates and times, fixing unconventional date formats, and using functions like datevalue, timevalue, edate, eomonth, networkdays, workday, and weeknum. We will also cover building simple timesheets, formatting times, and share additional tips for calculating various date-related values. This course is intended for experienced Excel users.

Navigation

Prev: X02
Next: Lesson 01

Keywords

TechHelp Excel, Excel 2010 dates and times, Excel date functions, Excel time functions, custom date format Excel, date arithmetic Excel, Excel date difference, Excel time difference, ExcelYearfrac, ExcelEdate, ExcelEomonth, ExcelNetworkdays, ExcelWorkday,

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Excel 2010 Expert Level 2, brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.

Today's class deals with dates and times in Excel. We will learn how Excel handles dates and times and how it stores them internally. We will learn about custom date and time codes. We will learn most of the popular date and time functions that Excel has to offer.

This class is designed to be used with Excel 2010, part of Microsoft Office 2010. If you are using an older version of Excel, you should visit my website at ExcelLearningZone.com and look for my older tutorials covering Excel 2000 through 2007. If you are using Excel 2007, you should have little difficulty following along as most of the concepts covered in this course are the same. If you are using an older version like 2003 or earlier, you really should either upgrade to Excel 2010 or purchase a tutorial for your version of Excel, which again you can find on my website. This is an expert-level course from Microsoft Excel 2010.

This class was designed for the user who has a good amount of experience with Excel and has completed all five courses in my beginner series plus expert level 1. The prerequisite for this course is my Excel 2010 Expert Level 1 course. That course covers functions, absolute references, named cells, referencing values on other sheets, and we cover all the popular text functions. If you have not taken this course, I strongly recommend you go to my website right now, ExcelLearningZone.com, and look for Expert Level 1 before taking this course.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. My beginner courses are for novice users who have little or no experience with Microsoft Excel. They are designed to give you an overview of the basic features and cover just what you need to know to be productive.

The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and we will cover more functions, features, tips, and techniques for power users.

After you have mastered the expert classes, move up to the advanced lessons. You will learn how to record macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer-level courses will teach you how to program in Visual Basic for Applications for Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Microsoft Office applications.

Each of my series is broken down into different levels. For example, the beginner series contained five different levels, which you should have taken before this course. This is the second level of the expert series, so you should have taken Level 1 before this course. Each level teaches you new and different topics in Microsoft Excel, building on the lessons in the previous levels. When you have finished all the expert classes, you will move up to the advanced series, and finally the developer series.

Now let us take a more detailed look at exactly what we are going to learn in today's class.

In lesson one, we are going to go over Excel dates and times. We are going to learn some different valid date time formats. We will learn how Excel handles dates and times internally, and we will learn about date arithmetic.

In lesson two, we are going to learn a little bit about customizing date formats, and we will learn some custom date and time codes.

In lesson three, we are going to look at some date time functions. We will look at now and today, year, month, day, hour, minute, second, week, day, date, and time.

In lesson four, we are continuing on with date and time functions. We will learn how to calculate the difference between two dates in the number of days, the number of months, the number of years. We will learn a couple of different functions for determining the difference in whole years, including yearfrac, date, diff, and we will learn about the int function for rounding a number down to the nearest integer.

In lesson five, we are continuing on with date time functions. We will learn how to fix bad dates if someone else gives you a spreadsheet that has dates that are not in a conventional format. I will show you some tricks for fixing those. We will cover the datevalue and timevalue functions. We will learn about edate, eomonth, or the end of month, to figure the last day of the month. Networkdays to determine the difference of workdays between two dates. The workday function and the weeknum function that calculated the week number.

In lesson six, we are continuing on with date time functions. We will learn how to calculate the difference between two times to build a simple timesheet. We will see how to format those times in different ways so you can see either hours and fractions of an hour or hours and minutes.

In lesson seven, we will learn some additional date time tips and tricks. I will show you how to calculate a bunch of different dates. For example, the first and last day of any given month. How many days a month has. What quarter a date falls in. How to figure out the first day of the year. The last day of the year. What day number in the year you are on. And how many days are left in the year. Then we will learn some additional tricks for formatting time. I will show you some things like how to display fractions of a second and lots more.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel LearningZone Interactive Student Forums. If you are watching this course using my custom video player software or online in my web theater, you should see the student forum for each lesson appear in a small window next to the class videos if you have an active internet connection. Here, you will see all of the questions that other students have asked, as well as my responses to them, and any other comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting ExcelLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then replay the lesson from the beginning and follow along with my examples. Actually create the same spreadsheet that I make in the video. Build a spreadsheet with me step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forums.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Quiz

Q1. What is the main focus of this course?
A. Dates and times in Excel
B. Creating charts in Excel
C. Advanced database management
D. Writing VBA macros in Word

Q2. Which prerequisite is strongly recommended before taking this course?
A. Expert Level 1
B. Beginner Level 1
C. Advanced Level 3
D. Developer Level 1

Q3. What does the beginner series of courses cover?
A. Basic features to help novice users be productive
B. Advanced project automation
C. Creating custom Excel add-ins
D. Web integration with Excel

Q4. Which topic is NOT covered in this expert-level course?
A. VBA programming and automation
B. Custom date and time codes
C. Date arithmetic in Excel
D. Date and time functions

Q5. What will you learn about in Lesson One?
A. Excel dates and times, valid formats, internal storage, and arithmetic
B. Creating charts with multiple axes
C. Designing custom ribbon tabs
D. Network security in Excel

Q6. Which of the following functions is used to calculate the difference of workdays between two dates?
A. Networkdays
B. Countif
C. Average
D. Sumproduct

Q7. What is the recommended approach to getting the most from the course?
A. Watch each lesson completely, then follow along and build the spreadsheet yourself
B. Try to memorize every formula
C. Skip lessons that seem uninteresting
D. Only read the transcript without watching

Q8. When should you start applying the material to your own projects?
A. After mastering the sample spreadsheet from the lesson
B. Immediately after watching the lesson
C. Never, as the lessons are theoretical
D. Only after finishing the developer-level series

Q9. If you get stuck or do not understand something, what should you do?
A. Watch the video again or post questions in the student forums
B. Stop learning altogether
C. Skip to the next lesson
D. Look for answers on unrelated websites

Q10. What is covered in Lesson Four about dates?
A. Calculating the difference between two dates in days, months, or years
B. Conditional formatting for dates
C. Importing data from the web
D. Creating pivot tables for dates

Q11. Which function helps to fix unconventional or bad dates in a spreadsheet?
A. Datevalue
B. Vlookup
C. Transpose
D. Offset

Q12. What kind of tips and tricks are covered in Lesson Seven?
A. Calculating the first and last day of a month, what quarter a date is in, day numbers in a year, and more
B. Creating bar charts
C. Importing PDF data
D. Printing large spreadsheets

Q13. Which course series teaches you Visual Basic for Applications programming?
A. Developer series
B. Beginner series
C. Expert series
D. Advanced series

Q14. What is the purpose of the expert series of courses?
A. To provide more in-depth knowledge and techniques for experienced users
B. To introduce Excel to complete beginners
C. To teach web development skills
D. To cover only advanced VBA programming

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone covers advanced techniques for working with dates and times in Excel 2010. This is the second course in the expert-level series. My name is Richard Rost, and I'll be guiding you through how Excel handles dates and times, both on the surface and behind the scenes. I'll also introduce you to custom date and time codes and some of the most popular functions Excel offers in this area.

This course is specifically created for Excel 2010, which is part of Microsoft Office 2010. If you are using Excel 2007, most of what I teach will still apply with minimal differences. However, if you're working with Excel 2003 or an earlier version, you should either consider upgrading or find a tutorial appropriate to your version, which is available on my website.

Before taking this class, you should already be comfortable with Excel. Ideally, you have finished the five beginner-level courses and the first expert-level course, which covers important concepts like functions, absolute references, named cells, referencing data across sheets, and common text functions. If you have not taken Expert Level 1 yet, I recommend you complete that first to get the most out of today's lesson.

My series is divided into four tiers: beginner, expert, advanced, and developer. The beginner courses are great for those new to Excel and focus on fundamental skills. The expert series, where we are now, is meant for those who are already comfortable with the basics and are ready to explore more powerful techniques and features. Advanced courses dive into topics such as recording macros, building user forms, creating templates, and adding sophisticated functionality. Finally, the developer-level training covers programming in Visual Basic for Applications, which lets you automate and customize Excel extensively.

Each tier is broken down into separate levels. For instance, there are five beginner levels, and this course is the second expert-level class. Every new level builds upon the previous one, introducing fresh topics and increasingly complex techniques.

Now, let me give you a lesson outline for today's class.

In the first lesson, we will examine how Excel manages dates and times, including various valid date and time formats, internal storage methods, and how to perform calculations involving dates (known as date arithmetic).

Lesson two covers customizing how dates and times are displayed. We will look at custom codes you can use to show dates and times in exactly the format you need.

In lesson three, we move on to Excel's built-in date and time functions. You'll learn about functions such as NOW and TODAY, as well as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, DATE, and TIME.

Lesson four continues with date and time functions, with a focus on determining the difference between two dates. We'll find out how to count the number of days, months, or years between dates, exploring different functions like YEARFRAC, DATEDIF, and INT, which helps when you need to round down to the nearest whole number.

In lesson five, we'll discuss how to handle and fix problematic date entries, especially if you get a spreadsheet from someone else with unconventional formats. I will show you ways to repair these using DATEVALUE and TIMEVALUE. You'll also learn about EDATE, EOMONTH (to find the last day of a given month), NETWORKDAYS (for calculating workdays between dates), WORKDAY, and WEEKNUM (to find the week number of a particular date).

Lesson six is about calculating the difference between two times, for example to track hours worked on a timesheet. We'll also look at different ways to format those time differences, such as showing hours and fractions of an hour, or hours and minutes.

In lesson seven, we round things out with advanced date and time tips. I'll show you how to calculate the first and last days of any month, determine how many days are in a month, identify a date's quarter, pinpoint the start and end of the year, determine the current day number and how many days remain in the year. You'll also see various formatting tricks for displaying times, including how to show fractions of a second and more.

If you have questions during the course, you can always participate in the Excel Learning Zone Interactive Student Forums. If you are watching from my video player or online, the forum for each lesson will be right beside the video when you are connected to the internet. Here you will find questions from other students, my responses, and additional comments. I recommend you read through those when you start each lesson, and don't hesitate to post your own questions and comments.

If you're watching this without internet access, you can visit the forums later at ExcelLearningZone.com/forums.

To learn best, I suggest watching each lesson all the way through first, just to get the overview. Then, go back and follow along with my examples. Build the same spreadsheets as I do in the lesson, step by step. Master those before applying the concepts to your own projects. And if at any point you get stuck, watch the lesson again or let me know what the problem is on the student forums.

Above all, keep an open mind. Excel may seem complex at first, but once you become familiar with it, you'll realize it is quite user friendly.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

How Excel handles dates and times internally
Valid date and time formats in Excel
Date arithmetic in Excel
Customizing date formats
Custom date and time codes
Popular date and time functions
NOW and TODAY functions
YEAR, MONTH, DAY functions
HOUR, MINUTE, SECOND functions
WEEKDAY, DATE, and TIME functions
Calculating date differences in days, months, years
YEARFRAC and DATEDIF functions
INT function for rounding down
Fixing unconventional date formats
DATEVALUE and TIMEVALUE functions
EDATE and EOMONTH functions
NETWORKDAYS and WORKDAY functions
WEEKNUM function
Calculating time differences (timesheets)
Formatting time as hours and fractions or minutes
First and last day of a given month
Number of days in a month
Determining quarter from a date
Calculating first and last day of year
Day number in the year
Days remaining in the year
Displaying fractions of a second in time formats

Article

Welcome to this expert tutorial on working with dates and times in Microsoft Excel. In this guide, you will learn how Excel handles dates and times internally, how to use custom date and time formats, and how to apply a wide range of date and time functions to solve real-world problems.

Let us begin by understanding how Excel stores dates and times. Excel treats dates as serial numbers, where each whole number represents a day starting from January 1, 1900. For example, January 1, 1900, is serial number 1, and January 2, 1900, is serial number 2. This numbering continues upward for each day. Times are stored as decimal values representing fractions of a day. For instance, noon is stored as 0.5 because it is halfway through the day. When you combine a date and a time, for example, January 1, 2020, at noon, Excel stores it as a single value that combines the day with the time fraction.

Understanding this system is crucial for working with date arithmetic in Excel. If you subtract one date from another, you get the number of days between them. For instance, if cell A1 contains 1/10/2023 and cell A2 contains 1/20/2023, =A2-A1 will return 10, as there are ten days between the two dates. The same applies to adding or subtracting times. If you have a start time of 8:00 AM and an end time of 5:00 PM, entering =end time - start time gives you the difference in days. To display the result in hours, you simply format the cell as [h]:mm.

Excel lets you format dates and times in various ways. The default date format is typically month/day/year (like 1/25/2023) but you can customize this. Right-click on a cell, choose Format Cells, then select Date or Custom and choose or create the format you want, like "yyyy-mm-dd" for a four-digit year first, or "dddd, mmm dd, yyyy" for a long date format. For times, you can use formats like "h:mm AM/PM" or "hh:mm:ss" to show hours, minutes, and seconds.

Excel also supports custom formatting codes. When creating a custom date, you can use codes like "yy" for a two-digit year, "yyyy" for a four-digit year, "mm" for a two-digit month, and "mmmm" for the full month name. For times, "hh" is hours, "mm" is minutes, and "ss" is seconds. If you want to show both date and time, you can combine these, such as "mm/dd/yyyy hh:mm AM/PM".

Excel provides many built-in functions for working with dates and times. The TODAY() function returns the current date, while the NOW() function returns the current date and time. YEAR(date), MONTH(date), and DAY(date) can extract each part from a given date. HOUR(time), MINUTE(time), and SECOND(time) work similarly for times.

Let us say you want to calculate someone's age. Subtract their birthdate from today's date using =TODAY() - birthdate. The result is the number of days, so to find years, divide by 365.25 (to account for leap years), like this: =(TODAY() - birthdate) / 365.25. Use the INT function to round down to a whole year: =INT((TODAY() - birthdate) / 365.25).

Sometimes you need to find the difference between two dates in specific units. The DATEDIF function helps here, even though it is not listed automatically in the function list. To use it, the syntax is =DATEDIF(start_date, end_date, unit), where unit is "d" for days, "m" for months, or "y" for years. For example, =DATEDIF(A1, A2, "m") shows the complete months between two dates. The YEARFRAC function is also useful for returning the fractional difference in years: =YEARFRAC(start_date, end_date).

To find the last day of the month, use EOMONTH(start_date, months). =EOMONTH(A1, 0) gives the last day of the month for the date in A1. To count only working days between two dates, use NETWORKDAYS(start_date, end_date), which ignores weekends (and you can specify holidays). The WORKDAY function calculates a date before or after a certain number of working days, useful for project deadlines.

Week numbers can be calculated with WEEKNUM(date). If you need to know which quarter a date falls in, use =INT((MONTH(date) - 1) / 3) + 1.

If you receive dates in unconventional formats, Excel may not recognize them automatically. In this case, you can use the DATEVALUE function to convert a text string that looks like a date into an actual date serial number. For example, =DATEVALUE("01-25-2023") turns the text into an Excel-recognized date. Similarly, use TIMEVALUE to convert a text time to a serial number for time.

Suppose you want to build a time sheet to calculate work hours. If you have start and end times (say, in A1 and B1), use =B1-A1 to get the time difference, and format the result cell as either "h:mm" for hours and minutes or "[h]:mm" if you want to display totals over 24 hours.

To extract more information from dates, you might want to know the first or last day of any month. For the first day, use =DATE(YEAR(A1), MONTH(A1), 1), replacing A1 with your date cell. For the last day, use =EOMONTH(A1, 0). To find out the number of days in a month, subtract the first day from the last day and add one: =EOMONTH(A1, 0) - DATE(YEAR(A1), MONTH(A1), 1) + 1.

To determine what day number in the year a date is (for example, January 15th would be day 15), use =A1 - DATE(YEAR(A1), 1, 0). To find how many days are left in the year, subtract your date from December 31: =DATE(YEAR(A1), 12, 31) - A1.

If you want to display fractions of a second in your times, use custom formatting like "hh:mm:ss.00" for hundredths of a second or "hh:mm:ss.000" for thousandths.

If you want to automate or extend your solutions, you might explore programming Excel with Visual Basic for Applications (VBA). For instance, you could write a VBA macro to quickly fill a range with today's date or generate a series of dates.

Here is a simple VBA macro example that enters the current date into a selected cell:

Sub InsertToday()
ActiveCell.Value = Date
End Sub

To use this, open the Visual Basic editor, insert a new module, and paste the code above. Then assign it to a button or run it as needed.

As you practice, it is helpful to create a new spreadsheet and try each of these examples. Start by entering some sample dates and times, experiment with the formulas, and use the Format Cells dialog to change how your results appear. If you encounter unexpected results, double check that your cells are formatted as dates or times as appropriate.

By understanding how Excel stores and manipulates dates and times, as well as the available functions and formatting options, you will be able to tackle a wide range of practical problems, from timesheets and project schedules to financial analysis and reporting. Remember to take your time, experiment with each of these techniques, and refer back here whenever you need a refresher on date and time operations in Excel.