Excel is fraught with nuances, especially when working with dates or times. In this webcast Excel expert David H. Ringstrom, CPA covers the gamut of date and time related functions and features in Excel. He’ll start out with showing data entry techniques for managing date inputs, and work through a variety of worksheet functions. You’ll also discover the Timeline feature for filtering pivot tables based on date ranges, using Conditional Formatting to identify specific dates, using Data Validation to enforce data entry requirements for dates and times, and much more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
Publication Date: May 2020
Designed For
Practitioners seeking to work more effectively in Microsoft Excel.
Topics Covered
- Contrasting the NOW function to the TODAY function
- Restricting users to enter dates within a given range or before/after a given date
- Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to today's date
- Building a series of dates with the Fill Series command
- Color-coding date ranges with conditional formatting
- Understanding the nuances of calculating the difference between two dates
- Preventing column widths from resizing within pivot tables
- Calculating dates in the past or future with the EDATE function
- Pairing the EOMONTH and SEQUENCE functions together to create a dynamic column of period end dates
- Filtering transactions by date or date range
- Copying dates within an Excel worksheet in the manner you want
- Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges
Learning Objectives
- Recognize how to select the arguments for the EOMONTH function in Excel
- Describe which Excel feature that Timelines complement
- Recognize valid filtering options in Excel
- Identify how worksheet function returns data
- Describe the TEXT function
- Recognize which commands appear on which Excel menu
- Describe the DATEDIF function
- Recognize which TIMEVALUE function is interchangeable with other function
Level
Basic
Instructional Method
Self-Study
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
None
Advance Preparation
None
Instructor
David H. Ringstrom
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches over 200 webinars each year. His Excel courses are based on over 30 years of consulting and teaching experience. David’s catchphrase is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. He's currently writing a book titled "Exploring Excel's Hidden Treasures". To learn more about David, you can view his
LinkedIn profile and follow him on
Facebook or
Twitter (@excelwriter).