Calculating effective time from start/end date+time
 

Hi
I have two columns with date + time like this

Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12

If i just sum up the difference in minutes between each row then I
will
get the total time in minutes but I'm looking for the effective time.

Some of the records are overlapping like record 1 and 2.
The effective time in record 1-2 is just 3h 17 minutes since record 2
fits inside the frame of the first record.

Is there an easy way to calculate this ?
Hope anyone can help
Stefan

hi, Stefan !

... two columns with date + time like this
Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12
... sum up the difference ... between each row ... will get the total ... minutes but I'm looking ... effective time.
Some ... records are overlapping ... record 2 fits inside the frame of the first record [...]

might this could work?
assuming record entries in range A2:B[n]
[C2] -first record- 'by default'... formula: =b2-a2
[C3] -from second record-... formula is: =(b3max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column -number format [h]:mm<-

hth,
hector.

Hi
Thanks for the help but there are some errors for example
11/08/2004 19:00 11/08/2004 22:17 3:17
11/08/2004 19:30 11/08/2004 21:24 0:00
11/08/2004 22:30 11/08/2004 23:50 1:20
11/09/2004 00:00 11/09/2004 00:45 0:45 <-
11/09/2004 00:30 11/09/2004 04:31 4:01 <-
11/09/2004 02:00 11/09/2004 03:20 0:00

The 4th and 5th record should show a total of 4:31 not 4:46

And if 3 records overlap this wont work :(
It will probably take some vb script to solve this.

Regards Stefan


"Héctor Miguel" wrote in message ...
hi, Stefan !

... two columns with date + time like this
Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12
... sum up the difference ... between each row ... will get the total ... minutes but I'm looking ... effective time.
Some ... records are overlapping ... record 2 fits inside the frame of the first record [...]

might this could work?
assuming record entries in range A2:B[n]
[C2] -first record- 'by default'... formula: =b2-a2
[C3] -from second record-... formula is: =(b3max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column -number format [h]:mm<-

hth,
hector.

Hi Stefan,

With Starts (A1:Ax) and Ends (B1:Bx),
in C1, the following Array (Ctrl-Shift-Enter) formula:

=MAX(0,MIN(IF(EndsB1,IF(StartsB1,Starts,B1),B1))-
MAX(IF(Starts<A1,IF(Ends<A1,A1,Ends)),A1))

Copy C1 till Cx
Sum C1:Cx somewhere and format this cell as [h]:mm

Regards,

Daniel M.

"Stefan Stridh" wrote in message
om...
Hi
Thanks for the help but there are some errors for example
11/08/2004 19:00 11/08/2004 22:17 3:17
11/08/2004 19:30 11/08/2004 21:24 0:00
11/08/2004 22:30 11/08/2004 23:50 1:20
11/09/2004 00:00 11/09/2004 00:45 0:45 <-
11/09/2004 00:30 11/09/2004 04:31 4:01 <-
11/09/2004 02:00 11/09/2004 03:20 0:00

The 4th and 5th record should show a total of 4:31 not 4:46

And if 3 records overlap this wont work :(
It will probably take some vb script to solve this.

Regards Stefan


"Héctor Miguel" wrote in message
...
hi, Stefan !

... two columns with date + time like this
Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12
... sum up the difference ... between each row ... will get the total ...
minutes but I'm looking ... effective time.
Some ... records are overlapping ... record 2 fits inside the frame of the
first record [...]

might this could work?
assuming record entries in range A2:B[n]
[C2] -first record- 'by default'... formula: =b2-a2
[C3] -from second record-... formula is: =(b3max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column -number format [h]:mm<-

hth,
hector.

Hi Daniel
I've tried your solution and it works as long as there aren't 3 overlapping
times like this (should be 1h 25min for those 3)

11/24/2004 17:00 11/24/2004 17:40 0:40
11/24/2004 19:00 11/24/2004 20:15 1:15 <
11/24/2004 19:00 11/24/2004 20:15 1:15 <
11/24/2004 19:30 11/24/2004 20:25 0:10 <
11/24/2004 22:30 11/25/2004 2:25 3:55


Thanks for all the help
Regards Stefan

"Daniel.M" wrote in message
...
Hi Stefan,

With Starts (A1:Ax) and Ends (B1:Bx),
in C1, the following Array (Ctrl-Shift-Enter) formula:

=MAX(0,MIN(IF(EndsB1,IF(StartsB1,Starts,B1),B1))-
MAX(IF(Starts<A1,IF(Ends<A1,A1,Ends)),A1))

Copy C1 till Cx
Sum C1:Cx somewhere and format this cell as [h]:mm

Regards,

Daniel M.

"Stefan Stridh" wrote in message
om...
Hi
Thanks for the help but there are some errors for example
11/08/2004 19:00 11/08/2004 22:17 3:17
11/08/2004 19:30 11/08/2004 21:24 0:00
11/08/2004 22:30 11/08/2004 23:50 1:20
11/09/2004 00:00 11/09/2004 00:45 0:45 <-
11/09/2004 00:30 11/09/2004 04:31 4:01 <-
11/09/2004 02:00 11/09/2004 03:20 0:00

The 4th and 5th record should show a total of 4:31 not 4:46

And if 3 records overlap this wont work :(
It will probably take some vb script to solve this.

Regards Stefan


"Héctor Miguel" wrote in message
...
hi, Stefan !

... two columns with date + time like this
Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12
... sum up the difference ... between each row ... will get the total
...
minutes but I'm looking ... effective time.
Some ... records are overlapping ... record 2 fits inside the frame
of the
first record [...]

might this could work?
assuming record entries in range A2:B[n]
[C2] -first record- 'by default'... formula: =b2-a2
[C3] -from second record-... formula is: =(b3max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column -number format [h]:mm<-

hth,
hector.

"Stefan Stridh" wrote...
I have two columns with date + time like this

Startdate Enddate
11/08/2004 19:00 11/08/2004 22:17
11/08/2004 19:30 11/08/2004 21:24
11/08/2004 22:30 11/08/2004 23:50
11/09/2004 00:00 11/09/2004 00:31
11/09/2004 00:30 11/09/2004 04:31
11/09/2004 02:00 11/09/2004 03:20
11/09/2004 15:00 11/09/2004 15:12

If i just sum up the difference in minutes between each row then I
will get the total time in minutes but I'm looking for the effective
time.

Some of the records are overlapping like record 1 and 2.
The effective time in record 1-2 is just 3h 17 minutes since record 2
fits inside the frame of the first record.

Is there an easy way to calculate this ?

If the ranges above were A1:A7 for start date/time and B1:B7 for end
date/time with headings in A1:B1, then try this formula in C3 (yes, col C
row *3*).

=MAX(B$1:B2)-MAX(IF(COUNT(B$1:B1),B$1:B1,0),A2)

Then copy C3 and paste into C2:C7.

"Harlan Grove" wrote...
....
If the ranges above were A1:A7 for start date/time and B1:B7 for end
date/time with headings in A1:B1, then try this formula in C3 (yes, col C
row *3*).

=MAX(B$1:B2)-MAX(IF(COUNT(B$1:B1),B$1:B1,0),A2)

Then copy C3 and paste into C2:C7.

Change the C3 formula to

=MAX(B$2:B3)-MAX(IF(COUNT(B$2:B2),B$2:B2,0),A3)

hi, all !

Harlan Grove wrote in message ...
Change the C3 formula to
=MAX(B$2:B3)-MAX(IF(COUNT(B$2:B2),B$2:B2,0),A3)

copying formula into C2[*] switches absolute-relative references for count function...
[C3] =MAX(B$2:B3)-MAX(IF(COUNT(B$2:B2),B$2:B2,0),A3)
[C2] =MAX(B$2:B2)-MAX(IF(COUNT(B1:B$2),B1:B$2,0),A2) <=[*]
and, is causing a 'lack'... leaving 'out' [of the records] the 'first' time-counting [in C2]
a -very minor- change in C3 formula could it be...
=MAX(B$2:B3)-MAX(B$1:B2,A3)
[totally based on Harlan's proposal]

cordially,
hector.

Hi Héctor, Harlan,

and, is causing a 'lack'... leaving 'out' [of the records] the 'first'
time-counting [in C2]
a -very minor- change in C3 formula could it be...
=MAX(B$2:B3)-MAX(B$1:B2,A3)

Yep. Very nice formula.

It requires the STARTS entries to be sorted (ascending).

Regards,

Daniel M.