Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Excel Expert 2

Age	Subject	From
3 years	Time Calculations	Maurizio Emanuele

Subscribe to Excel Expert 2
Get notifications when this page is updated

Intro

In lesson 2 of my Microsoft Excel 2010 Expert series, we will focus on working with dates and times in Excel. You'll learn how Excel stores dates and times, explore custom date and time codes, and see how to use many of Excel's key date and time functions like NOW, TODAY, DATE, TIME, NETWORKDAYS, and more. We'll also cover formatting, date arithmetic, calculating differences between dates and times, and tips for fixing non-standard date formats. This lesson is designed for experienced users who have already completed my beginner courses and Expert Level 1.

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'll learn how Excel handles dates and times and how it stores them internally. We'll learn about custom date and time codes. We'll 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're 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're using Excel 2007, you should have little difficulty following along as most of the concepts covered in this course are the same. If you're 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 for Microsoft Excel 2010. This class was designed for users who have a good amount of experience with Excel and have 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 all the popular text functions. If you haven't taken this course, I strongly recommend you go to my website, 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're 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'll cover more functions, features, tips, and techniques for power users.

After you've mastered the expert classes, move up to the advanced lessons. You'll 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 contains 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 one before this course. Each level teaches you new and different topics in Microsoft Excel, building on the lessons in the previous levels. When you've finished all the expert classes, you'll move up to the advanced series, and finally the developer series.

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

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

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

In lesson three, we're going to look at some date and time functions. We'll look at NOW and TODAY, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, DATE, and TIME.

In lesson four, we're continuing on with date and time functions. We'll learn how to calculate the difference between two dates in the number of days, the number of months, and the number of years. We'll learn a couple of different functions for determining the difference in whole years, including YEAR, FRAC, DATEDIF, and we'll learn about the INT function for rounding a number down to the nearest integer.

In lesson five, we're continuing on with date and time functions. We'll learn how to fix bad dates if someone gives you a spreadsheet that has dates that aren't in a conventional format. I'll show you some tricks for fixing those. We'll cover the DATEVALUE and TIMEVALUE functions. We'll 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 calculates the week number.

In lesson six, we're continuing on with date and time functions. We'll learn how to calculate the difference between two times to build a simple timesheet. We'll 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'll learn some additional date and time tips and tricks. I'll 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're on, and how many days are left in the year. Then we'll learn some additional tricks for formatting time. I'll show you 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 Interactive Student Forums. If you're 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're 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.

Don't try to apply what you're learning right now to other projects until you've mastered the sample spreadsheet. If you get stuck or don't understand something, watch the video again from the beginning or tell me what's 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'll see that it's really easy to use.

Quiz

Q1. What is the primary focus of this Excel 2010 Expert Level 2 course?
A. Basic Excel features and navigation
B. Dates and times in Excel, including handling, formatting, and functions
C. Macros and VBA programming
D. Chart creation and formatting

Q2. Which lesson covers custom date and time codes in Excel?
A. Lesson one
B. Lesson two
C. Lesson four
D. Lesson six

Q3. In which version of Excel is this course primarily designed to be used?
A. Excel 2003
B. Excel 2007
C. Excel 2010
D. Excel 2016

Q4. Which prerequisite course should students take before this course?
A. Beginner Level 5
B. Advanced Level 1
C. Expert Level 1
D. Developer Level 1

Q5. What type of learners are the expert series of courses designed for?
A. People with no experience in Excel
B. Users comfortable with Excel seeking to learn deeper concepts and advanced features
C. Children learning computers for the first time
D. only those needing to learn charting

Q6. What does the advanced course series focus on after expert classes?
A. Basic functions and formulas
B. Visual Basic programming
C. Macros, user forms, templates, and advanced Excel features
D. Introduction to PowerPoint

Q7. Which of the following is NOT mentioned as a date or time function covered in the class?
A. NOW
B. DAY
C. AVERAGE
D. WEEKDAY

Q8. What advice does the instructor give for getting the most out of the course?
A. Apply lessons immediately to your ongoing projects
B. Only watch the video, do not practice
C. First watch each lesson all the way through, then replay and follow along to build the sample spreadsheet
D. Take handwritten notes only

Q9. Which function is mentioned for determining the week number in Excel?
A. WORKDAY
B. WEEKNUM
C. NETWORKDAYS
D. EOMONTH

Q10. Where can students post their questions about the course material?
A. Only at their local library
B. In the Excel Interactive Student Forums online
C. Nowhere, questions are not allowed
D. By emailing Microsoft support

Q11. What is one suggested method for fixing dates not in a conventional format?
A. Re-enter all dates manually
B. Use random formatting
C. Use DATEVALUE and TIMEVALUE functions
D. Delete problematic data

Q12. What concept is introduced in lesson six?
A. Charting and graphics
B. Building a simple timesheet and calculating differences between times
C. Protecting worksheets
D. Sorting and filtering data

Q13. What is the purpose of the INT function as described in the course?
A. To create integer sequences
B. To round a number up to the nearest integer
C. To format text
D. To round a number down to the nearest integer

Q14. What are some additional tips discussed in lesson seven?
A. Creating charts and graphs
B. Formatting text in cells
C. Calculating dates like first/last day of month and day number in the year
D. Printing settings in Excel

Q15. What mindset does the instructor encourage students to maintain?
A. Only focus on memorizing shortcuts
B. Be open-minded and persistent, as Excel becomes easier with practice
C. Learn only by reading manuals
D. Expect to master everything on first try

Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-B; 11-C; 12-B; 13-D; 14-C; 15-B

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 expert-level concepts related to working with dates and times in Excel 2010. I'm your instructor, Richard Rost. In this course, I'll show you how Excel manages dates and times, how to work with various formats, and how to use many of the key date and time functions available in Excel.

This course is specifically designed for Excel 2010, which is part of Microsoft Office 2010. If you have an older version of Excel, I suggest visiting my website to find tutorials for Excel 2000, 2002, 2003, or 2007. Most of what I cover applies to Excel 2007 as well, but if you are using something even older, it's best to get a tutorial geared for that version or consider upgrading to Excel 2010.

This is an expert-level class, so it is best suited for people with a solid background in Excel. If you have completed my five beginner courses and the first level of my expert series, you're ready for this one. Expert Level 1 covers fundamentals like functions, absolute references, named cells, referencing data on other sheets, and popular text functions. If you are not comfortable with those yet, I recommend watching Expert Level 1 before moving on to this course.

My curriculum is divided into four main groups: beginner, expert, advanced, and developer. The beginner series is perfect for newcomers, offering a foundation in Excel's most essential tools. The expert classes, like the one we're focused on now, offer a deeper look at more advanced features, functions, and techniques for those who already have some experience under their belts.

Once you've worked through the expert topics, you'll be ready for the advanced classes. In those, you'll learn how to record macros, build user forms, create templates, and access other advanced features that are especially helpful if you want to take your spreadsheets to the next level of professionalism and functionality.

Developer-level lessons go even deeper and will teach you how to use Visual Basic for Applications (VBA) with Excel. With these skills, you'll be able to create custom programs, automate your Excel work, and closely integrate your spreadsheets with other Microsoft Office applications.

Each series contains several progressively more involved levels. For example, the beginner series has five different levels, all of which you should complete before getting to the expert material. This course is the second part of the expert series, so you should have already worked through Level 1. Each new course builds on the previous material, so by progressing through them in order, you will gradually develop a thorough understanding of Excel.

Now, let me give you an overview of what we will be covering today.

In the first lesson, we'll take a close look at how Excel stores and manages dates and times. We'll examine valid formats and see how date arithmetic works.

The second lesson will focus on customizing date formats and exploring custom date and time codes, giving you greater control over how your data appears.

In lesson three, we'll go through some of the main date and time functions. These include NOW, TODAY, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, DATE, and TIME.

Next, in lesson four, we'll continue with more date and time functions. You'll learn how to calculate differences between two dates in terms of days, months, and years, using functions like YEAR, FRAC, DATEDIF, and INT to handle rounding and more.

Lesson five will tackle cleaning up data. I'll show you how to fix spreadsheets that come with poorly formatted dates. We will cover DATEVALUE and TIMEVALUE functions and look at functions for end-of-month calculations like EDATE and EOMONTH. You'll also learn about NETWORKDAYS to count only workdays, as well as the WORKDAY and WEEKNUM functions.

Lesson six will show you how to work with time differences, which comes in handy for timesheets. You'll see how to format time to display fractions of an hour or break it down into hours and minutes.

In lesson seven, I'll share additional tips and tricks with dates and times. You'll see how to calculate things like the first and last day of any month, how many days are in a month, what quarter a particular date is in, how to find the first or last day of the year, what day of the year it is, and how many days remain until the year's end. We'll also go through some advanced time formatting options, such as displaying fractions of a second.

If you need help with any of the topics from today's class, you're welcome to post your questions in the Excel Interactive Student Forums. If you are using my video player or watching online, you'll find the forums right next to the videos as long as you're connected to the web. Here, you can read questions from other students, see my answers, and join in the discussions.

I recommend starting each lesson by reading the forum discussions, then feel free to post your own questions if something is unclear. If you are not connected while watching the videos, you can always visit the forums on ExcelLearningZone.com.

To get the best results from this course, I suggest watching each lesson straight through once without practicing along. After that, watch the lesson again and work through the examples with me, building the same spreadsheets that I create in the video. Do not rush to use the new skills on your actual work until you have mastered these examples. If you find yourself stuck, watch the lesson again or ask for help in the forums.

Most of all, approach the material with an open mind. Excel might seem overwhelming at first, but once you understand how it all fits together, you'll see it becomes much easier to use.

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 stores dates and times internally
Valid date and time formats in Excel
Performing date arithmetic in Excel
Customizing date formats
Using custom date and time codes
NOW and TODAY functions
YEAR, MONTH, and DAY functions
HOUR, MINUTE, and SECOND functions
WEEKDAY function
DATE and TIME functions
Calculating differences between dates in days, months, and years
Using YEARFRAC and DATEDIF for whole years calculation
Using the INT function for rounding down numbers
Fixing improperly formatted dates
Using DATEVALUE and TIMEVALUE functions
Using EDATE and EOMONTH functions
NETWORKDAYS and WORKDAY functions
Calculating week numbers with WEEKNUM
Calculating time differences for timesheets
Formatting time as hours and fractions or hours and minutes
Calculating the first and last days of a month
Finding the number of days in a month
Determining which quarter a date is in
Identifying the first and last day of the year
Calculating the day number in the year
Calculating days left in the year
Formatting time to display fractions of a second