# Date and time function overview

The ability to store and manipulate dates and times is one of Excel's most powerful features. Using Excel's built-in date and time functions, you can easily add days and months to particular dates; create dynamic date-based fields; and perform complex time-based calculations.

However, dates and times in Excel can be a bit difficult to wrap your head around due to the complex way in which the program stores them internally. In this tutorial, we'll take a look at the

## Date storage in Excel

The most important thing to learn about dates in Excel is that they are not stored as month, day, and year combinations, but rather as encoded **January 1, 1900**. In other words, the date

To see this phenomenon in action, enter some dates into a series of cells, like so:

As you can see, Excel automatically recognizes our input as dates and formats it appropriately. But, in reality, these dates are being stored as serial numbers that represent each day's distance from January 1, 1900.

Let's change the number formatting on these cells from "Date" to "General" to reveal the serial numbers behind our dates:

This may seem like an odd way for Excel to store dates, but the serialization process provides an important benefit: it allows us to add and subtract dates just like numbers. Let's say, for example, that we wanted to find the number of days between two dates — for example, the number of days between

="5/10/2019" -"5/6/2019"

Output:4

Likewise, we can also add a set number to a given date like so:

="5/10/2019" +2

Output:5/12/2019 , or serial number43,597

## The DATE function

OK, so we know that dates are stored in Excel as serial numbers. And we know that Excel is smart enough to recognize cell inputs that look like dates and convert them to serial numbers automatically. But is there a way to generate a date's serial number formulaically?

Of course there is — the

The syntax for

=DATE (year ,month ,day )

Fed a given

=DATE (1900 ,1 ,1 )

Output:1/1/1990 , or serial number1

In the above example, the formula outputs

=DATE (2019 ,5 ,6 )

Output:5/6/2019 , or serial number42,129

In the above example, the formula outputs

## Time storage in Excel

Now that we've got a handle on dates, how should we think about storing time values in Excel? Fortunately, things here are a lot easier now that we've mastered the

Times are also stored via serialization, but they are represented with fractional values rather than whole integers. Each day is represented by one whole serial number, and since there are 24 hours in a day, each hour is represented as

For the purposes of serialization, each day entered into Excel starts at 12:00am. So a date-time combination entered as

Note that a time value entered without a date will serialize without a leading integer — in other words, on

If you want to use minutes and seconds in Excel, they work the same way as hours. Since a minute is 1/1,440 of a day, it is represented in Excel as

Full time values are entered in the format,

## The TIME function

Like the

The syntax for

=TIME (hour ,minute ,second )

Provided a given

=TIME (0 ,0 ,0 )

Output:12:00 AM , or serial number0

Above, the formula outputs

=TIME (5 ,23 ,36 )

Output:5:23:36 AM , or serial number0.2247222...

Above, the formula outputs

Note that we can also serialize a specific date-time combination by using the

=DATE (2019 ,5 ,6 )+TIME (5 ,23 ,36 )

Output:5/6/2019 5:23:36 AM , or serial number42,129.2247222...

Those are the basics of

## Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on how to automate your work using Excel.

- Why the
**TRUE and FALSE**functions are so important... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...