Excel stores dates as numbers beginning with January 1st, 1900 with the value 1 and each subsequent day being one number larger. This allows adding/subtracting number to/from dates, as well as subtracting dates to calculate day, month or year differences. Times are stored as fractional portions of days.
- [Voiceover] In this workbook, called 09-Date,…we're looking at the first worksheet called Dates and Times.…Excel's ability to handle date and time math…is a powerful tool.…All revolves around the idea that when you enter a date,…you're actually putting in the number.…So for example, this cell B-1 contains a date,…you don't necessarily know what value is being stored here,…but the date system begins January 1st, 1900,…that's day one.…January 2nd is day two, so the unit of measure here…is one equal a day.…
So when we have two day entries here,…these are actually values being stored in the background.…I can subtract these, equal,…a later date minus an earlier date.…Get an answer.…If I reserve the formula, I'd get a minus 70,…but that's a 70 day difference.…Now, occasionally when you get data from other sources,…you might get data in a certain column,…it might have a date heading, but sometimes…these have been converted into numbers along the way.…And we can simulate what that looks like,…by going on the Home tab,…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use dates and times in Excel formulas