Calculating effective time from start/end date+time

S

Stefan Stridh

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
 
H

Héctor Miguel

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 [...]
Click to expand...

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: =(b3>max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column ->number format [h]:mm<-

hth,
hector.
 
S

Stefan Stridh

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 said:
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 [...]
Click to expand...

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: =(b3>max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column ->number format [h]:mm<-

hth,
hector.
Click to expand...
 
D

Daniel.M

Hi Stefan,

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

=MAX(0,MIN(IF(Ends>B1,IF(Starts>B1,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 said:
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" <[email protected]> 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
Click to expand...
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: =(b3>max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column ->number format [h]:mm<-

hth,
hector.
Click to expand...
Click to expand...
 
M

Melvin8

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 said:
Hi Stefan,

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

=MAX(0,MIN(IF(Ends>B1,IF(Starts>B1,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 said:
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" <[email protected]> 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: =(b3>max($b$2:b2))*(b3-a3)
drag/copy/down, and you can now sum 'C' column ->number format [h]:mm<-

hth,
hector.
Click to expand...
Click to expand...
Click to expand...
 
H

Harlan Grove

Stefan Stridh said:
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 ?
Click to expand...

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.
 
H

Harlan Grove

Harlan Grove said:
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.
Click to expand...

Change the C3 formula to

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

Héctor Miguel

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)
Click to expand...

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.
 
D

Daniel.M

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)
Click to expand...

Yep. Very nice formula.

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

Regards,

Daniel M.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time query 9
Excel date intervals look up 1
countif with time 3
airline standby worksheet 7
How do I find a match for three variables to determine the result? 4
Peak call times/hours 2
Modifying a complex date formual to count months 4
WORK HOURS DIFFERENCE BETWEEN TWO DATES 12

Top