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.
--
Atri

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

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

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)?
--
Atri


"Heera" wrote:

>
> 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
>

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")
--
David Biddulph

"Heera" <(E-Mail Removed)> wrote in message
news:2e73fdbb-51fc-4f1a-88ab-(E-Mail Removed)...
> 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
>

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.
--
Atri


"Heera" wrote:

> 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
>
>

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)

Hello Heera!

Thanks for all the help!
--
Atri


Heera skrev:

> 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)
>