Excel - Calculating difference in hours between two dates
I've come across a situation before when analyzing data, let's say a log file from a webserver, thats been exported to a Microsoft Excel worksheet and I find myself needing to calculate the difference in hours between two dates to know how many times a file or something was accessed.
So below I will outline the steps that can be preformed to output the difference in hours with a single decimal place when you have two given values like so:
Wed 12/31/2008 4:56 PM
Thu 1/01/2009 2:04:28 AM
The first step we will do is break the date apart into two cells so that we can perform calculations on it easier since the “Wed” and “Thu” text in there will cause errors in most functions. Follow these steps to break apart the data:
- Select the cells you wish to break apart
- Click on “Data”
- Click on “Text to Columns”
- On the first screen choose “Fixed width” and click Next
- Now you need to set the column breaks so that there is one after the day (Wed or Thu) and the next column break after the PM or AM. This is what will actually separate the data into two columns.
- Click Next
- On this screen we will set the column data format, from the list please choose “Date”.
- Click Finish
Your data will now be separated into two columns. Now we will use a combination of the TEXT and ROUNDUP function to return the values we are looking for. Enter the following value into the cell where you would like the value displayed:
Notice with the function above you will need to change the values of A2 and A1 to match the cells which your values are stored in. Remember that the nearest date (A1) must be subtracted from the furthest date (A2).