Dealing with Dates and Times in Microsoft Excel

Welcome to the course, Dealing with Dates and Times in Microsoft Excel. While entering a date into a cell seems like a simple task, Excel often complicates matters with automatic formatting changes, hidden serial numbers, and calculation quirks. In this session, spreadsheet expert David Ringstrom, CPA, takes you on a deep dive into the mechanics of date and time management, helping you navigate the "dark alleys" of Excel so you can work smarter and avoid common pitfalls.

This course is designed to bridge the gap between simple data entry and complex temporal analysis. You will learn why Excel behaves unexpectedly when summing times, how to transform text entries into usable dates, and how to utilize a suite of powerful functions to calculate deadlines, quarters, and elapsed time. By the end of this presentation, you will have the tools to ensure your date-based data is accurate, consistent, and visually effective.

Key topics covered in this course include:

• Creating and managing date series effectively using the Fill Handle and the often-overlooked Fill Series command.
• Understanding the underlying serial number system and how to handle the two-digit year window versus four-digit requirements.
• Converting text-based dates and times into numeric values using Text to Columns, DATEVALUE, and TIMEVALUE.
• Performing accurate calculations, such as summing hours exceeding 24 hours using custom formatting and calculating the difference between dates using the DATEDIF function.
• Using powerful functions like NETWORKDAYS.INTL to exclude weekends and holidays, and identifying fiscal or calendar quarters with CHOOSE and ROUNDUP.
• Differentiating between volatile functions like NOW and TODAY, and projecting future or past dates with EOMONTH and EDATE.
• Implementing Data Validation to restrict user input to specific date ranges and using Conditional Formatting to highlight deadlines.
• Optimizing charts to remove gaps caused by non-working days and using Timeline filters for PivotTables.

Whether you are creating amortization schedules, tracking project timelines, or importing data from external databases, this course provides the hard-fought knowledge necessary to master dates and times in Excel.