Excel stores dates and times in a helpful format. Dates are stored as integers and times are stored as decimal values that are relative to the 24 hours in a day. Because of this, it’s easy to combine date and time values into a single date-time value. In this tutorial, you’ll learn how to combine date and time in Excel using step-by-step methods.
By the end of this tutorial, you’ll have learned how to combine date and time values using simple addition. Want to learn how to separate a date or a time from a date-time instead? Check out this in-depth guide on how to do this: How to Separate Date and Time in Excel.
Understanding How Excel Stores Dates and Times
Excel has a clever way of storing date and time values. The date portion is stored as an integer value, with January 1, 1900, as the first date. Any date beyond that is incremented by 1. For example:
- January 3, 1900, would be equal to 3
- November 23, 2023, would be 45253
The time part of an Excel date and time variable is a decimal value ranging from 0 through 1, where the value is relative to the 24 hours in a day. For example:
- 5:51 AM is equal to 0.25389
- 10:24 PM is equal to 0.93349
Now that you know how Excel stores dates and times, let’s dive into how to separate dates and times from a date time variable in Excel.
How to Combine a Date and Time in Excel To a DateTime Using Addition
How can you combine a date and time in Excel?
To combine a date and time value in Excel into a single date-time value simply add the two values together. Because Excel stores dates as whole numbers and times as decimals, by adding them together you get date-time values. For example, if your date is in cell A2 and your time in cell B2, you can use =A2+B2 to get your time value.
Let’s use an easy-to-follow example to show how to combine a column of dates and times into a single date-time value.
How to Combine Date and Time Values into Date-Time Values in Excel
Load your dataset containing date values and time values
Load your dataset with the date values and time values in separate columns. In this example we have seven different values that we’ll want to combine into a single date-time column.
Use the Formula =A2+B2 to Combine Date and Time Together
Because Excel date values are stored as whole numbers and times are stored as decimals, you can combine date and time values by adding them together. In the example above, we’re using the formula =A2+B2 to add the first date and time values together.
Drag the Fill Handle Down to Combine All Dates and Times
To copy the formula down for all date and time values, click and drag the fill handle (the little crosshairs in the bottom right of a cell) all the way down to the last set of values. When you let go of the mouse, Excel will copy and paste the formula all the way down, combining the remaining dates and times together.
In the example above, we were able to easily join together date and time values into date-time values. Because of how Excel stores date and time values, we’re able to use simple addition to combine them into a single date-time value.
Conclusion
In this tutorial, you learned how to use Excel to combine individual date and time values into a date-time value. You first learned how Excel stores date values and how it stores time values. Since date values are stored as whole numbers, while time values are stored as decimals. Because of this, you learned that you can easily add these values together using the + operator. You walked through a step-by-step example illustrating how to work with entire columns containing date and time values. Using the process of the tutorial, you were able to learn how to combine the values into a single date-time column.
Additional Resources
To learn more about related topics, check out the tutorials below: