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 0x<infinity. Does anybody know how to do
this?

=B1-A1, format as [h]:mm or just[h]
(FormatCellsNumberCustom)

--
Kind regards,

Niek Otten

"andoh" wrote in message
...
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 0x<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]
(FormatCellsNumberCustom)

--
Kind regards,

Niek Otten

"andoh" wrote in message
...
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 0x<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]
(FormatCellsNumberCustom)

--
Kind regards,

Niek Otten

"andoh" wrote in message
...

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 0x<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]
(FormatCellsNumberCustom)

--
Kind regards,

Niek Otten

"andoh" wrote in message
...

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 0x<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" wrote in message
...
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]
(FormatCellsNumberCustom)

--
Kind regards,

Niek Otten

"andoh" wrote in message
...

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 0x<infinity. Does anybody know how
to do
this?