Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically enter date and time but only update once. | New Users to Excel | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
calculating date time ranges | Excel Worksheet Functions |