Calculating elapsed time

I would like to calculate the elapsed time in hours of 2 cells with the
format dd/mm/yyyy hh:mm:ss. The two cells represent the start and finish time
of a job and the elapsed time is 0>x<infinity. Does anybody know how to do
this?

=B1-A1, format as [h]:mm or just[h]
(Format>Cells>Number>Custom)

--
Kind regards,

Niek Otten

"andoh" <andoh@discussions.microsoft.com> wrote in message
news:ED5CF1B3-17B9-4A98-B2FA-580D4B7D45A1@microsoft.com...
>I would like to calculate the elapsed time in hours of 2 cells with the
> format dd/mm/yyyy hh:mm:ss. The two cells represent the start and finish
> time
> of a job and the elapsed time is 0>x<infinity. Does anybody know how to do
> this?

Thanks for your reply Niek but this doesn't work or I'm doing something
wrong. If you notice the cells contain more than just hh:mm and the dates are
not always the same.

"Niek Otten" schreef:

> =B1-A1, format as [h]:mm or just[h]
> (Format>Cells>Number>Custom)
>
> --
> Kind regards,
>
> Niek Otten
>
> "andoh" <andoh@discussions.microsoft.com> wrote in message
> news:ED5CF1B3-17B9-4A98-B2FA-580D4B7D45A1@microsoft.com...
> >I would like to calculate the elapsed time in hours of 2 cells with the
> > format dd/mm/yyyy hh:mm:ss. The two cells represent the start and finish
> > time
> > of a job and the elapsed time is 0>x<infinity. Does anybody know how to do
> > this?
>
>
>

Hi

Niek's suggestion works absolutely fine.
Are you sure you are putting the [ ] around the h in the custom format of
hh:mm?
It needs to be [hh]:mm not hh:mm.
The latter will only give the hours difference, as the calculation won't
roll past 24 hours.

Whether you use [h] or [hh] won't make any difference other than showing a
leading zero for number of hours less than 10.

Regards

Roger Govier


andoh wrote:
> Thanks for your reply Niek but this doesn't work or I'm doing something
> wrong. If you notice the cells contain more than just hh:mm and the dates are
> not always the same.
>
> "Niek Otten" schreef:
>
>
>>=B1-A1, format as [h]:mm or just[h]
>>(Format>Cells>Number>Custom)
>>
>>--
>>Kind regards,
>>
>>Niek Otten
>>
>>"andoh" <andoh@discussions.microsoft.com> wrote in message
>>news:ED5CF1B3-17B9-4A98-B2FA-580D4B7D45A1@microsoft.com...
>>
>>>I would like to calculate the elapsed time in hours of 2 cells with the
>>>format dd/mm/yyyy hh:mm:ss. The two cells represent the start and finish
>>>time
>>>of a job and the elapsed time is 0>x<infinity. Does anybody know how to do
>>>this?
>>
>>
>>

Thanks Roger,

but I can't get it to workMaybe its to do with the format of the A1 & B1.
The formula in C1 is B1-A1 and I've formatted C1 with both [hh]:mm and [h]:mm
but neither seem to work. Here is an example of the format of the cells:

Cell A1: 17/10/2005 10:09:13 Cell B1: 18/10/2005 14:09:11 Cell C1: #VALUE!

Any tips would be greatly appreciated,
Regards,
Andrew O'Hara

"Roger Govier" schreef:

> Hi
>
> Niek's suggestion works absolutely fine.
> Are you sure you are putting the [ ] around the h in the custom format of
> hh:mm?
> It needs to be [hh]:mm not hh:mm.
> The latter will only give the hours difference, as the calculation won't
> roll past 24 hours.
>
> Whether you use [h] or [hh] won't make any difference other than showing a
> leading zero for number of hours less than 10.
>
> Regards
>
> Roger Govier
>
>
> andoh wrote:
> > Thanks for your reply Niek but this doesn't work or I'm doing something
> > wrong. If you notice the cells contain more than just hh:mm and the dates are
> > not always the same.
> >
> > "Niek Otten" schreef:
> >
> >
> >>=B1-A1, format as [h]:mm or just[h]
> >>(Format>Cells>Number>Custom)
> >>
> >>--
> >>Kind regards,
> >>
> >>Niek Otten
> >>
> >>"andoh" <andoh@discussions.microsoft.com> wrote in message
> >>news:ED5CF1B3-17B9-4A98-B2FA-580D4B7D45A1@microsoft.com...
> >>
> >>>I would like to calculate the elapsed time in hours of 2 cells with the
> >>>format dd/mm/yyyy hh:mm:ss. The two cells represent the start and finish
> >>>time
> >>>of a job and the elapsed time is 0>x<infinity. Does anybody know how to do
> >>>this?
> >>
> >>
> >>
>

Probably your dates are actually text. You can check with the ISTEXT()
function.
If so:
Format the cells as dd/mm/yyyy h:mm and re-enter the dates and times (F2,
ENTER)

--
Kind regards,

Niek Otten

"andoh" <andoh@discussions.microsoft.com> wrote in message
news:EA250B87-F3F4-41E7-9F1E-2B11AA3CA6A2@microsoft.com...
> Thanks Roger,
>
> but I can't get it to workMaybe its to do with the format of the A1 & B1.
> The formula in C1 is B1-A1 and I've formatted C1 with both [hh]:mm and
> [h]:mm
> but neither seem to work. Here is an example of the format of the cells:
>
> Cell A1: 17/10/2005 10:09:13 Cell B1: 18/10/2005 14:09:11 Cell C1: #VALUE!
>
> Any tips would be greatly appreciated,
> Regards,
> Andrew O'Hara
>
> "Roger Govier" schreef:
>
>> Hi
>>
>> Niek's suggestion works absolutely fine.
>> Are you sure you are putting the [ ] around the h in the custom format of
>> hh:mm?
>> It needs to be [hh]:mm not hh:mm.
>> The latter will only give the hours difference, as the calculation won't
>> roll past 24 hours.
>>
>> Whether you use [h] or [hh] won't make any difference other than showing
>> a
>> leading zero for number of hours less than 10.
>>
>> Regards
>>
>> Roger Govier
>>
>>
>> andoh wrote:
>> > Thanks for your reply Niek but this doesn't work or I'm doing something
>> > wrong. If you notice the cells contain more than just hh:mm and the
>> > dates are
>> > not always the same.
>> >
>> > "Niek Otten" schreef:
>> >
>> >
>> >>=B1-A1, format as [h]:mm or just[h]
>> >>(Format>Cells>Number>Custom)
>> >>
>> >>--
>> >>Kind regards,
>> >>
>> >>Niek Otten
>> >>
>> >>"andoh" <andoh@discussions.microsoft.com> wrote in message
>> >>news:ED5CF1B3-17B9-4A98-B2FA-580D4B7D45A1@microsoft.com...
>> >>
>> >>>I would like to calculate the elapsed time in hours of 2 cells with
>> >>>the
>> >>>format dd/mm/yyyy hh:mm:ss. The two cells represent the start and
>> >>>finish
>> >>>time
>> >>>of a job and the elapsed time is 0>x<infinity. Does anybody know how
>> >>>to do
>> >>>this?
>> >>
>> >>
>> >>
>>