Calculating time between 2 entries

dcecil

New Member
Joined
Mar 5, 2002
Messages
3
I have a column that has a date/time format of dd/mm/yy hhmm. I need to calculate the amount of hours and minutes (x:xx) between certain entries in the column. Is there any way to do this? I can send a sample if that would make it easier.

Thanks,
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Use =ABS(A1-A2) where cells A1 and A2 contain date values. Format the cell containing this formula as [h]:mm
 
Upvote 0
On 2002-03-06 07:30, dcecil wrote:
I have a column that has a date/time format of dd/mm/yy hhmm. I need to calculate the amount of hours and minutes (x:xx) between certain entries in the column. Is there any way to do this? I can send a sample if that would make it easier.

Thanks,

Just subtract one value from the other and format your result as [h]:mm (e.g., =B1-A1).

Regards,
 
Upvote 0
Sorry guys, but both of the solutions that were given, return an error ($VALUE!). I think the date in the beginning of the field is messing it up. The fields looks like this: 3/6/02 0900 and 3/6/02 1515, which would return a value of 6:15. Hope this helps.

Thanks,
 
Upvote 0
On 2002-03-06 07:52, dcecil wrote:
Sorry guys, but both of the solutions that were given, return an error ($VALUE!). I think the date in the beginning of the field is messing it up. The fields looks like this: 3/6/02 0900 and 3/6/02 1515, which would return a value of 6:15. Hope this helps.

Thanks,

So your data is actually being read by Excel as a text value? Do you ever have the times span across a day (e.g., 3/6/02 0900 in one cell and 3/7/02 0300 in another)?
 
Upvote 0
Okay, assuming your higher date/time is in cell B1 and the lower one is in cell A1, try this formula:

=TIMEVALUE(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1))-TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1))+DATEVALUE(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1))-DATEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1))

NOTE - this formula assumes the format of your times is always the same. That is, the last two characters always represents minutes.

Regards,
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top