Overview
-
Learn tricks and techniques for optimizing the use of dates and times in Excel 2016.
Syllabus
-
Introduction
- Welcome
- Using the exercise files
- Understanding how Excel records and stores dates
- Understanding how Excel records and stores times
- 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
- Creating standard date/time formats
- Creating custom date formatting
- Exploring keystroke shortcuts
- Formatting time for hours over 24; AutoCalculate adjustments
- 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
- 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
- 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
- Next steps
