Calculate the difference between two times and show dd:hh:mm:ss

[Atri]

Hello!

I use Excel version 2003 SP2 and have a question about calculating the
difference between two times. I have the two times in different cells and
use this formula to calculate: A1-B2=C2. I use the cell format time 37:30:55
in cell C2 and that works. The result is shown as for exampel 78:04:43
(hh:mm:ss). I want to show the result in days, hours, minuts and seconds.
Is there a format or function I can use to solve this problem? It is much
for user-friendly to show days, hours and minutes, in stead of only hours and
minuts. I will appreciate any help.

 

[Heera]

Hi,

Select the cell right click go the "Format Cell..."

Select Numbers Tab
Category : Custom and type the below mentioned line.

dd "Days"-hh:mm

Regards

Heera

 

[Heera]

Hi,


Select the cell right click go the "Format Cell..."


Select Numbers Tab
Category : Custom and type the below mentioned lines and see the
results.

If the time is 88:34:56

Type this dd "Days"-hh:mm & the result will be 03 Days-16:34
Type this dd-hh:mm & the result will be 03-16:34
Type this dd - hh:mm & the result will be 03 - 16:34

Regards


Heera

 

[Atri]

thanks! It worked perfectly!

Is it possible to only calculate the time within 8 AM and 6 PM each day (in
stead of 24 hours)?

 

[David Biddulph]

But try it with 888:34:56 or 8888:34:56.

You may want something like =INT(A2)&" days "&TEXT(A2,"hh:mm:ss")
or =INT(D4)&":"&TEXT(D4,"hh:mm:ss")

 

[Atri]

I didn't explain my last question so well, so I shall try to explain it
better. Our helpdesk is open from 8 AM to 6 PM. I am calculating the time
from an problem is registered to the problem is solved. I have an open-time
and a close-time. A problem can be registered 4 PM and be solved 10 AM the
next day. Excel will calculate the solution-time to 18 hours in this
example, but within our opening-hours it will only be 4 hours. Is it
possible to tell Excel to only count the hours within our opening time who is
8 AM to 6 PM? I know that nearly "everything" is possible in Excel, but my
knowledge about Excel is unfortunately not so good.

 

[Heera]

Hi,

Difference between 6:00 PM today to 8:00 AM Tomorrow is of 14:00:00.

1. Now put 14:00:00 in cell A1.
2. Starting from A3 put the below mentioned dates and time.

Registered (Start from Cell A3)
8/18/08 13:30
8/18/08 13:50
8/18/08 15:30
8/18/08 17:30
8/18/08 8:30 (End at Cell A8)

3. Starting from B3 put the below mentioned dates and time.

Solved (Start from Cell B3)
8/19/08 13:30
8/19/08 13:30
8/19/08 13:30
8/20/08 13:30
8/18/08 13:30(End at Cell B8)

4. Starting from C3 put the below mentioned formula.

Formula (Start From Cell C3)
=IF(DAY(B4)-DAY(A4)>=1,(B4-A4-$A$1*(DAY(B4)-DAY(A4))),B4-A4)
=IF(DAY(B5)-DAY(A5)>=1,(B5-A5-$A$1*(DAY(B5)-DAY(A5))),B5-A5)
=IF(DAY(B6)-DAY(A6)>=1,(B6-A6-$A$1*(DAY(B6)-DAY(A6))),B6-A6)
=IF(DAY(B7)-DAY(A7)>=1,(B7-A7-$A$1*(DAY(B7)-DAY(A7))),B7-A7)
=IF(DAY(B8)-DAY(A8)>=1,(B8-A8-$A$1*(DAY(B8)-DAY(A8))),B8-A8) (End at
Cell C8)

 

[Atri]

Hello Heera!

Thanks for all the help!