I am looking for a way to calculate differences between dates and times.
More in details :
I have a column with datas under this format : dd/mm/yyyy hh:mm These datas have been manually collected and the elapsed time between two acquisitions varies drastically.
I would like to calculate the time difference between 2 concecutive rows and give the result in either hh:mm or decimal format.
Examples : 01/01/2009 00:00 could be the starting time for this example 01/01/2009 02:15 gives 02:15 or 2.25 01/01/2009 06:00 gives 03:45 or 3.75 02/01/2009 15:30 gives 35:30 or 35.50 03/01/2009 11:10 gives 23:40 or 23.67 05/01/2009 08:40 gives 45:30 or 45.50 etc...
Does anybody have an idea of how to perform this ? Thanks for your help.
Please try the following: 1. Create a column to the right of the date column. 2. Double-click on the title of this column to open the Worksheet Column Formatdialog. In the dialog, set Format to Time and Display to HH:mm. Click OK. 3. Right-click on the title of this column and then select Set Column Values from the shrot-cut menu. 4. Suppose the source column is column A and it has 4 rows. In the dialog, enter the formula and set the "from row" and "to" as follows: Note that "to" is set to "3", because the source column has 4 rows and 4-1=3. After clicking OK, you can see the results.
I wonder why I didn't think of doing this by myself ...
It works as weel if you leave "auto" for rows settings and I found it is even best to do col(B)=col(A)[i]-col(A)[i-1] because like that the result of the calculation i is facing the data i and not data i-1.
More in details and taking the same examples :
With the formula you recommended : col(B)=col(A)[i+1]-col(A)[i] one obtains : 01/01/2009 00:00 => 02:15 01/01/2009 02:15 => 03:45 01/01/2009 06:00
And with this formula col(B)=col(A)[i]-col(A)[i-1] one obtains : 01/01/2009 00:00 01/01/2009 02:15 => 02:15 01/01/2009 06:00 => 03:45
Calculations on dates and times
Posted - 01/05/2009 : 10:02:22 AM
