Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Stefan Stridh
 
Posts: n/a
Default 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
  #2   Report Post  
Héctor Miguel
 
Posts: n/a
Default

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.


  #3   Report Post  
Stefan Stridh
 
Posts: n/a
Default

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.

  #4   Report Post  
Daniel.M
 
Posts: n/a
Default

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.



  #5   Report Post  
Melvin8
 
Posts: n/a
Default

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.







  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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


  #7   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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)


  #8   Report Post  
Héctor Miguel
 
Posts: n/a
Default

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.


  #9   Report Post  
Daniel.M
 
Posts: n/a
Default

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
Help - Information with time and date PM Excel Discussion (Misc queries) 4 January 6th 05 09:25 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM
calculating date time ranges Patrick Excel Worksheet Functions 2 November 11th 04 06:07 AM


All times are GMT +1. The time now is 10:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"