I was wondering if anyone could help as I've spent a lot of time on
this and can't quite get this calculation to work. I am trying to find
out how long in time (24-hour clock) there is between A1 and B1 taking
into consideration the date.

Okay, here is my problem:

In A1 I have 08/04/2007 01:57:56
In A2 I have 09/04/2007 21:46:57
In A3 I have the formula =B2-A1
(all these cells are formatted as custom: dd/mm/yyyy hh:mm:ss)

The answer I get in A3 is 01/01/1900 19:49:01

Now, as far as I can see, there are indeed 19 hours, 49 minutes and 1
second time difference between the two cells (correct me if I'm
wrong), but the date is all wrong. The main thing I need is how many
days difference there is in addition to the time. In this case, there
are no whole days past between the two dates, but it does go through
midnight. From the result it seems there is a day difference (i.e. 19
hours, 49 minutes and 1 second time + 24 hours!).

I need to do this for a whole bunch of data with varying number of
days between each cell and I want to make sure I've got the formula
right. Does anyone know the correct formula please?

1. the difference is 49 hours 49 minutes and 1 second

2. format your cell to d [g]:mm:ss;@ or dd [g]:mm:ss;@ to see days and
hours...

pls click YES if this helped



On 27 Maj, 23:00, fluff wrote:
I was wondering if anyone could help as I've spent a lot of time on
this and can't quite get this calculation to work. I am trying to find
out how long in time (24-hour clock) there is between A1 and B1 taking
into consideration the date.

Okay, here is my problem:

In A1 I have 08/04/2007 01:57:56
In A2 I have 09/04/2007 21:46:57
In A3 I have the formula =B2-A1
(all these cells are formatted as custom: dd/mm/yyyy hh:mm:ss)

The answer I get in A3 is 01/01/1900 19:49:01

Now, as far as I can see, there are indeed 19 hours, 49 minutes and 1
second *time difference between the two cells (correct me if I'm
wrong), but the date is all wrong. The main thing I need is how many
days difference there is in addition to the time. In this case, there
are no whole days past between the two dates, but it does go through
midnight. From the result it seems there is a day difference (i.e. 19
hours, 49 minutes and 1 second time + 24 hours!).

I need to do this for a whole bunch of data with varying number of
days between each cell and I want to make sure I've got the formula
right. Does anyone know the correct formula please?

On 27 May, 22:11, Jarek Kujawa wrote:
1. the difference is 49 hours 49 minutes and 1 second

2. format your cell to d [g]:mm:ss;@ or dd [g]:mm:ss;@ to see days and
hours...

pls click YES if this helped

On 27 Maj, 23:00, fluff wrote:

I was wondering if anyone could help as I've spent a lot of time on
this and can't quite get this calculation to work. I am trying to find
out how long in time (24-hour clock) there is between A1 and B1 taking
into consideration the date.

Okay, here is my problem:

In A1 I have 08/04/2007 01:57:56
In A2 I have 09/04/2007 21:46:57
In A3 I have the formula =B2-A1
(all these cells are formatted as custom: dd/mm/yyyy hh:mm:ss)

The answer I get in A3 is 01/01/1900 19:49:01

Now, as far as I can see, there are indeed 19 hours, 49 minutes and 1
second *time difference between the two cells (correct me if I'm
wrong), but the date is all wrong. The main thing I need is how many
days difference there is in addition to the time. In this case, there
are no whole days past between the two dates, but it does go through
midnight. From the result it seems there is a day difference (i.e. 19
hours, 49 minutes and 1 second time + 24 hours!).

I need to do this for a whole bunch of data with varying number of
days between each cell and I want to make sure I've got the formula
right. Does anyone know the correct formula please?

Perfect! That has worked fine now. Many thanks for your help.