Calculating Differences in Date and Time Values With Microsoft Excel

In Excel, dates are stored as a number. Technically, the value of a date is stored internally as the number of days since 12/31/1899. To test this, just enter the number 1 in any cell, then right-click on that cell and select Format Cells. Change the format to Date and click OK. Now you’ll see the date 1/1/1900. That’s one day since 12/31/1899.
Conveniently, time values are stored internally as fractions of a day. So if you want to store the time 12:00 AM, that’s simply a 0. If you want 12:00 PM, that’s given a value of 0.5, or half a day. The other time values are calculated accordingly. 6:00 PM is equal to 0.75…courtesy office.com/setup

Knowing this, you can easily calculate the difference in days between two dates by simply subtracting them and making sure that number is formatted as a date. For example, in cell A1, enter the date 3/1/2018. In cell A2, enter the date 3/4/2018. Now in cell A3 you can subtract them:=A2-A1

The resulting value should be 3. There are three days between those two dates. Excel automatically formats this as a number for you. Calculating time values, however, gets a little trickier. Put the time value 5:00 PM in cell B1. Put 6:00 PM in cell B2. Now subtract them:=B2-B1

Excel returns the value of 1:00 AM as the result, which is not quite what you want. That’s OK. Just format this cell as a Number type (again, right-click and select Format Cells, choose Number, click OK). Now you see the value is 0.04. Remember, however, that Excel treats time and date values as whole days. So what you have now is 0.04 days. Let’s multiply that value by 24 to convert it to hours. Change cell B2 to read:=(B2-B1)*24

And now you can see the result is 1.0, which is what we expect. There is a 1 hour difference between 5 PM and 6 PM. This technique also works if you have a value that is a mixed date/time. For example, if you change B1 to “3/1/2018 6:00 PM” and change B2 to “3/2/2018 7:00 PM” you will see that there are 25 hours between those two times.
Once you get used to it, you’ll find that this is a much easier method for calculating the differences between dates and times than using all of the various functions that Excel provides.

Robert Morris a Microsoft Office expert has been working in the technical industry from last 5 year. As a technical expert, he has written technical blogs, manuals, white papers, and reviews for many websites such as www.office.com/setup

Leave a Reply

Your email address will not be published. Required fields are marked *