Helping your colleagues in office to sort out their problems always presents an opportunity for you to learn something new and a sense of satisfaction as well. Recently I experienced that in my office. After finding the solution to the problem, I felt that there might be many such people who would be facing this problem. That is the precise reason for this post.
My colleague had his data in Excel sheet in which he had captured date and time of a particular event in two separate cells. Just like cells B5 and C5 in the example below. He wanted the difference in two dates in terms of number of days and number of hours.
In this example, calculating the difference in time between the two events is not possible until and unless Date and Time are combined in one cell. This was achieved in the following manner:
1) Firstly data in column B was formatted into date format as DD/MM/YYYY format, which is commonly followed format in India. Date 1 was 25th April 2012 and Date 2 was 27th April 2012.
2) Data in column C was formatted in Time format as hh:mm format.
3) Now with date in cell B5 and time in cell C5, both were combined into one cell in cell D5 using the formula
=INT(B5)+MOD(C5,1)
4) After that data in cell D5 was formatted into "Custom" format as "dd/mm/yyyy hh:mm:ss" format.
5) Same thing was done for date 2 as well.
6) To calculate the number of days between the two dates, formula was entered in cell D10 as =D8-D5 and D10 cell was formatted in number format without decimal and 1000 separator.
7) To calculate the time difference between the two dates, formula was entered in cell D11 as =D8-D5 and cell D11 was formatted in "Custom" format as "[h]:mm"
2) Data in column C was formatted in Time format as hh:mm format.
3) Now with date in cell B5 and time in cell C5, both were combined into one cell in cell D5 using the formula
=INT(B5)+MOD(C5,1)
4) After that data in cell D5 was formatted into "Custom" format as "dd/mm/yyyy hh:mm:ss" format.
5) Same thing was done for date 2 as well.
6) To calculate the number of days between the two dates, formula was entered in cell D10 as =D8-D5 and D10 cell was formatted in number format without decimal and 1000 separator.
7) To calculate the time difference between the two dates, formula was entered in cell D11 as =D8-D5 and cell D11 was formatted in "Custom" format as "[h]:mm"
If you click on any cell with formula above, formula can be viewed in bottom right corner. Please keep in mind that Excel date and time formats are governed by regional settings of your machine and as a result, formats may appear different. However, logic remains the same.
Also on some machines, you may not be able to view the above embedded excel sheet directly. On such machines, you can click on that window to open it directly in Google Drive/Google docs.
Also on some machines, you may not be able to view the above embedded excel sheet directly. On such machines, you can click on that window to open it directly in Google Drive/Google docs.
