Calculate Duration.

Need Help..

I would like to subtract the end time(hh:mm:ss) from the start
time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
process.

Thanks.

=?Utf-8?B?QXJ0?= <Art@discussions.microsoft.com> wrote in news:EAEA51AD-
1838-4725-9E7D-48EF22FF1B66@microsoft.com:

> Need Help..
>
> I would like to subtract the end time(hh:mm:ss) from the start
> time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> process.
>
> Thanks.
>
>

Wouldn't you rather do it the other way round?

Start in A2
End in B2
Format of A2 and B2 to 13:30:55
Duration in C2: =B2-A2
Set format of C2 to [h]:mm:ss


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Hi Art

Sub test()
Dim t As Date, tt As Date
t = Now
'Your code
tt = Now
MsgBox Format((tt - t), "hh:mm:ss")
End Sub
--


XL2003
Regards

William
willwest22@yahoo.com


"Art" <Art@discussions.microsoft.com> wrote in message
news:EAEA51AD-1838-4725-9E7D-48EF22FF1B66@microsoft.com...
> Need Help..
>
> I would like to subtract the end time(hh:mm:ss) from the start
> time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> process.
>
> Thanks.
>

Thank you Dodo & William

"Art" wrote:

> Need Help..
>
> I would like to subtract the end time(hh:mm:ss) from the start
> time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> process.
>
> Thanks.
>

Dodo & William

I have encountered this error. Could you tell me what I'm missing.

c3 = 22:47:00
c2 = 00:01:00

answer: ##############

I only get the above answer when using the midnight time.





"Art" wrote:

> Thank you Dodo & William
>
> "Art" wrote:
>
> > Need Help..
> >
> > I would like to subtract the end time(hh:mm:ss) from the start
> > time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> > process.
> >
> > Thanks.
> >

Did you use:
=c2-c3

If you did, then when excel sees negative times (or dates), it shows them as
####'s.

You can avoid this by using the 1904 base date system.

Tools|Options|calculation tab is where you'd toggle this.

But if you have any dates in that workbook, they'll change by 4 years and one
day.

If c2 is the following day, you could do this:

=c2-c3+if(c2<c3,24,0)
or shorter
=c2-c3+(c2<c3)

A more robust way would be to enter both the date and time in each cell.

Art wrote:
>
> Dodo & William
>
> I have encountered this error. Could you tell me what I'm missing.
>
> c3 = 22:47:00
> c2 = 00:01:00
>
> answer: ##############
>
> I only get the above answer when using the midnight time.
>
>
>
> "Art" wrote:
>
> > Thank you Dodo & William
> >
> > "Art" wrote:
> >
> > > Need Help..
> > >
> > > I would like to subtract the end time(hh:mm:ss) from the start
> > > time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> > > process.
> > >
> > > Thanks.
> > >

--

Dave Peterson

Thanks Dave that did it..

"Dave Peterson" wrote:

> Did you use:
> =c2-c3
>
> If you did, then when excel sees negative times (or dates), it shows them as
> ####'s.
>
> You can avoid this by using the 1904 base date system.
>
> Tools|Options|calculation tab is where you'd toggle this.
>
> But if you have any dates in that workbook, they'll change by 4 years and one
> day.
>
> If c2 is the following day, you could do this:
>
> =c2-c3+if(c2<c3,24,0)
> or shorter
> =c2-c3+(c2<c3)
>
> A more robust way would be to enter both the date and time in each cell.
>
> Art wrote:
> >
> > Dodo & William
> >
> > I have encountered this error. Could you tell me what I'm missing.
> >
> > c3 = 22:47:00
> > c2 = 00:01:00
> >
> > answer: ##############
> >
> > I only get the above answer when using the midnight time.
> >
> >
> >
> > "Art" wrote:
> >
> > > Thank you Dodo & William
> > >
> > > "Art" wrote:
> > >
> > > > Need Help..
> > > >
> > > > I would like to subtract the end time(hh:mm:ss) from the start
> > > > time(hh:mm:ss) to get the duration (HH:MM:SS) of how long a job took to
> > > > process.
> > > >
> > > > Thanks.
> > > >
>
> --
>
> Dave Peterson
>