Lynda Excel 2010: Working with Dates

Author: Dennis Taylor
Duration: 01h 20m
Released on: 7/28/2010
Exercise files: Yes
Software works on: Windows only

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

Content:

Introduction - 01m 33s

• Welcome - 01m 13s
• Using the exercise files - 00m 20s

1. Excel's Date/Time System - 05m 29s

• Understanding how Excel records and stores dates - 03m 56s
• Understanding how Excel records and stores times - 01m 33s

2. Creating Date/Time Entries - 12m 59s

• Looking at standard date/time entry options and acceptable alternatives - 02m 51s
• Using instant date/time entry - 01m 27s
• Using the TODAY and NOW functions for dynamic date/time entry - 03m 04s
• Using Auto Fill to enter date and time series - 05m 37s

3. Date/Time Formatting - 06m 17s

• Exploring keystroke shortcuts - 01m 45s
• Formatting time for hours over 24 - 02m 16s
• Creating custom date formatting - 02m 16s

4. Date/Time Functions - 17m 45s

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

5. Calculating with Dates and Times - 21m 09s

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

6. Using Dates and Times with Excel Commands - 15m 18s

• Using special date filters with date data - 03m 27s
• Using date controls in data validation rules - 05m 41s
• Using date functions in data validation rules - 03m 07s
• Converting unusually formatted dates into usable data (text to columns) - 03m 03s

Conclusion - 00m 18s

• Goodbye - 00m 18s