Excel 2016: Working with Dates and Times

Introduction

• Welcome
• Using the exercise files

1. Understanding the Excel Date/Time System

• Understanding how Excel records and stores dates
• Understanding how Excel records and stores times

2. Creating Date/Time Entries

• Looking at standard date/time entries and acceptable alternatives
• Using instant date and time entry
• Using TODAY and NOW functions for dynamic date/time entry
• Using AutoFill to enter date and time series rapidly

3. Formatting Dates and Times

• Creating standard date/time formats
• Creating custom date formatting
• Exploring keystroke shortcuts
• Formatting time for hours over 24; AutoCalculate adjustments

4. Working with Dates and Times in Functions

• EOMONTH and EDATE – tabulating end of months and future/past dates
• DATEDIF – tabulating date differences by year, month, and day
• WEEKDAY – determining the day of the week
• WEEKNUM and ISOWEEKNUM – calculating week numbers
• NETWORKDAYS – calculating working days
• WORKDAY – calculating ending date
• DATEVALUE and TIMEVALUE – converting text entries into dates and times

5. Calculating with Dates and Times

• Calculating date differences with formulas and the DAYS function
• Calculating time differences within and across days
• Calculating fiscal years and quarters
• Calculating the first or last day of the last, current, or future month
• Calculating holidays: Labor Day, Thanksgiving, Memorial Day, etc.
• Rounding time calculations to convenient intervals
• Using times with currency calculations

6. Using Dates/Times with Excel Commands

• Using special date filters with date data; advanced filter applications
• Using date/time controls and functions in data validation rules
• Using Flash Fill or Text to Columns to convert oddly formatted dates into usable data

Conclusion

• Next steps