Excel Date and Time Formulas Self-Taught

Ira's book, based on his Excel knowledge and experience since its inception in 1985, is a comprehensive presentation of Excel's amazing date and time formulas and functions. Topics covered include:

• How to create a time card in Excel, starting with a blank worksheet
• How to display calendar headings in Hindi, Chinese, Yoruba, French, Estonian, and many other languages
• How to construct a formulas that calculate Thanksgiving, Memorial Day, or Columbus Day for any year
• How to create a calendar a book club meeting that meets on the third Tuesday of the month
• How to calculate a person’s age in years, months, days, hours, minutes, and/or seconds
• How to import dates from external databases with the Text to Columns Wizard
• Why the date you enter isn’t the same as the date Excel displays
• When Excel thinks dates are text
• How Excel stores time
• How to create column headings with times separated by 20 minutes
• How to create a date stamp, a date and time that does not recalculate
• How to fix time calculations that are more than 24 hours
• What Excel thinks the century is for a 2 digit year, and how to change it
...and many more.

TABLE OF CONTENTS

1. Date Formulas
Entering a Date By Typing It In
Autoformatting: Why Excel Dates Are So Confusing
A Simple Trick To Prevent Autoformatting
The Date Serial Number: How Excel Stores A Date
The Reason For The 1900 and 1904 Date Systems
Converting Between The 1900 And 1904 Date Systems
How Excel Handles Leap Years
Formatting: How Excel Displays A Date
Displaying The Date In Other Languages
Excel’s Builtin Date Formats
Changing The Default Date Display Format
The General Format: What Excel Stores In Its Memory
Date Math: Calculating the Difference Between Two Dates
The DATE() Function: Entering an Excel Date
How DATE() Displays Errors
Two Methods For Entering Today’s Date
MONTH(), DAY(), and YEAR(): The Parts Of A Date
DATE() Math
How Excel Determines The Century For A 2 Digit Year
Converting Date Serial Numbers To Text And Back
TEXT(): Converting A Date Serial Number To Text
DATEVALUE(): Converting Date Text To A Date Serial Number

2. Excel’s Special Date Functions
WEEKDAY(): Day Of The Week
Starting The Weekday On A Different Day
WEEKNUM(): Week Number Of A Date In A Year
EOMONTH(): Last Day Of The Month
EDATE(): Number Of Months From a Date
YEARFRAC(): Part Of A Year Between Two Dates
DAYS360(): Date Interval Based on a 360 Day Year
WORKDAY(): Adding Or Subtracting Workdays From A Date
WORKDAY.INTL(): International Version of WORKDAY()
NETWORKDAYS(): Number Of Workdays Between Two Dates
NETWORKDAYS.INTL(): International Version of NETWORKDAYS()
DATEDIF()
ROMAN(): The Roman Numeral Of A Number

3. Time Formulas
Time Autoformatting
Times That Cross Over Past Midnight
Scenario: A Hospital Time Card Application
Step 1: Calculating First Shift Hours
Step 2: Turning a Negative Into A Positive
Step 3: Tweaking the Total Time
Another Tricky Date Calculation Problem To Solve
How To Round Time Intervals
Using CEILING() and FLOOR()
Using ROUND() and INT()
Formulas For Getting Parts Of The Time
NOW(): Getting the Exact Date And Time
Entering The Exact Time Without Recalculating
HOUR(), MINUTE(), SECOND()
TIME(): Constructing a Time Serial Number
TIMEVALUE(): Creating A Time Serial Number

4. Real-World Examples
Auto Filling Dates
Auto Filling a Cell Range With Date Increments
AutoFilling Day or Month Names
Supplying Autofill Hints
AutoFilling With a Date Formula
AutoFilling a Cell Range With Time Increments
Determining the Quarter Number of a Date
Finding The Nth Day Of The Week In A Month
Finding The Last Day Of The Week In A Month
Calculating A Person’s Age In Years
Method 1: Constructing A Complex IF Formula Using DATE
Method 2: Using DATEDIF
Converting Date Text In YYYYMMDD To A Date Serial Number
Text to Columns: Version 1
Text to Columns: Version 2
How It Was