How to calculate elapsed time in Excel? 2

[Guest_imported]

Got two columns in Excel sheet containing date and time in the following format
01/02/2001 10:00:00 PM

How can I subtract one column from the other to find difference in Hours?

 

[DaleWatson123321]

Ashraf,

This formula will give you precisely what you asked for - the difference in hours. It ONLY counts COMPLETED hours.
For example, if the difference is 28 hours and 59 minutes, it will count 28 hours.

If you need something different, then you will need to be MORE precise if specifying EXACTLY what you require.

But this formula will "get things started".

=((INT(1-(MOD(A1,1))+MOD(B1,1))+(INT(B1-A1)-1))*24)+(INT(MOD((1-(MOD(A1,1))+MOD(B1,1)),1)*24))

Copy it right from here into your worksheet. If it is copied as "merged cells", simply "un-merge" the cells.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

a1 =

b1 =

c1 = =(B1-A1)*24

Where A1 is start time
Where B1 is end time
Where C1 is formatted as a number

http://www.TheWordExpert.com

 

[DaleWatson123321]

Dreamboat,

Your solution is certainly MUCH shorter, and possibly what Ashraf wants.

Please note, however, that with your formula, if the difference is:

1) 2 hours and 30 minutes - will count as 2 hours.

2) 2 hours and 31 minutes - will count as 3 hours.

But your formula is worthy of a STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Hmmm. Dale: I haven't read your email yet and I'm probably slobbering...

I'm not sure what you mean. I put 4:30 pm in a1, I put 6:30 pm in cell b1 (they both default to today's date). I format c1 as a number with two decimals.

for 6:30 - 4:30, I get 2.00
for 6:31 - 4:30, I get 2.02

He asked for hours, but he didn't say he needed it to SHOW in hour format, like 2:00. I'm just assuming (OF COURSE! LOL) that he'd want it the same way I would. I want to know how long something occurred, I sure don't want 2:02 for two hours, two minutes.

So I guess it boils down to which format he's using. And you're a star with or without mine. Since you're already at the top of the list, I'll refrain. LOL LOL

http://www.TheWordExpert.com