Dates in Excel Explained

Add to Favorites
Author:

I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also times in Excel.

What is a Date in Excel?

Dates in Excel are actually just numbers that are formatted as dates.

If you take a date and select the cell and then set that cell's formatting to "General" it will look like this:

Cell A1 has the "date" and cell A2 has that same date formatted as "General."

As you can see, Excel stores dates in this weird format, but, it's actually not that weird and makes handling dates a lot easier.  Excel stores dates as numbers.

The dates actually start at January 1, 1900, which is represented with a 1, and add one for each day since then.  So Feb 10, 2016 is 42,409 days since January 1, 1900.

Look at what happens when you format 1 as a date (it appears as the date 1/1/1900):

Why Does this Matter?

The point to learning that dates are just numbers in Excel is because, then, you can more easily remember how to manage them.

Add a day to a date by adding 1. Add a week by adding 7.

Cells A4 and A5 show the formulas entered in cells B4 and B5.

As you can see, storing dates as numbers makes dates easier to work with because we can use simple math to adjust them.

Helpful Date Tutorials

This is just an intro to help you wrap your head around the concept of dates in Excel.  There are many more complex things that you can do with dates and also with times in Excel.

Here is a link to all of our date and time tutorials: Date and Time Tutorials for Excel

Notes

There is a lot to learn about dates; make sure to check out the link above to see all of our tutorials on that subject.

Download the accompanying spreadsheet so you can see the examples from this tutorial in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Years, Months, Days Between two dates in Excel
Tutorial: I have found a rather convoluted method to Calculate the Number of Years, Months and Days ...
How to Use Dates in Excel
Tutorial: Introduction Guide to using Dates in Excel - this tutorial will show you how to input and...
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allow...
Generate Random Dates in Excel
Tutorial: How to generate random dates in Excel. This tip shows you how to set a date range and the...
6 Tricks to Make Dates Work in Excel
Tutorial: Watch this tutorial if you work with dates! Quick and easy ways to reliably convert dates...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Years, Months, Days Between two dates in Excel
Tutorial: I have found a rather convoluted method to Calculate the Number of Years, Months and Days ...
How to Use Dates in Excel
Tutorial: Introduction Guide to using Dates in Excel - this tutorial will show you how to input and...
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allow...
Excel Forum