Lynda.com Excel 2010: Working with Dates and Times

Author:

In Excel 2010: Working with Dates and Times, Dennis Taylor shares his solutions for optimizing the use of dates and times in Excel 2010. This course explains what's going on behind the scenes when Excel stores dates and times, gives tips for entering dates and times, and shows options for date and time formatting. It also demonstrates the various date and time functions and shows how to calculate with dates and times in a range of scenarios. Exercise files are included with the course.

Topics include:

* Understanding how Excel records and stores dates and times
* Looking at standard date/time entry options and acceptable alternatives
* Using the TODAY and NOW functions
* Customizing date formats
* Exploring keyboard shortcuts
* Formatting time for hours over 24
* Calculating differences across dates and times
* Rounding calculations
* Working with holidays
* Validating with dates
* Converting formatted dates to usable data

Introduction 01:33
Welcome 01:13
Using the exercise files 00:20

1. Excel's Date/Time System 05:29
Understanding how Excel records and stores dates 03:56
Understanding how Excel records and stores times 01:33

2. Creating Date/Time Entries 12:59
Looking at standard date/time entry options and acceptable alternatives 02:51
Using instant date/time entry 01:27
Using the TODAY and NOW functions for dynamic date/time entry 03:04
Using Auto Fill to enter date and time series 05:37

3. Date/Time Formatting 06:17
Exploring keystroke shortcuts 01:45
Formatting time for hours over 24 02:16
Creating custom date formatting 02:16

4. Date/Time Functions 17:45
EOMONTH and EDATE: Calculating ends of months and future/past dates 04:30
DATEDIF: Calculating date differences by year, month, day, and more 04:17
WEEKDAY: Determining the day of the week 01:49
NETWORKDAYS: Calculating working days 02:26
WORKDAY: Calculating ending dates 01:56
DATEVALUE and TIMEVALUE: Converting text entries into dates and times 02:47

5. Calculating with Dates and Times 21:09
Calculating date differences across days, months, and years 01:45
Calculating time differences within and across days 04:56
Calculating fiscal years and quarters 05:24
Rounding time calculations to convenient intervals 04:42
Using times with currency calculations 02:44
Calculating holidays (Labor Day, Thanksgiving, Memorial Day, etc.) 01:38

6. Using Dates and Times with Excel Commands 15:18
Using special date filters with date data 03:27
Using date controls in data validation rules 05:41
Using date functions in data validation rules 03:07
Converting unusually formatted dates into usable data (text to columns) 03:03

Conclusion 00:18
Goodbye 00:18