|
Complicated Time Formula
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. |
Complicated Time Formula
Try this user defined function:
A2 = 09:15 B2 = 11:30 C1 = 09:00 D1 = 10:00 E1 = 11:00 F1 = 12:00 C2 = =PeriodExposure($A2,$B2,C$1,D$1) D2 = =PeriodExposure($A2,$B2,D$1,E$1) E2 = =PeriodExposure($A2,$B2,E$1,F$1) Function PeriodExposure(dStart As Date, dEnd As Date, dPeriodStart As Date, dPeriodEnd As Date) As Date Dim dtmTemp As Date If dStart < dPeriodStart And dEnd < dPeriodStart Or _ dStart = dPeriodEnd And dEnd = dPeriodEnd Then dtmTemp = 0 ElseIf dStart = dPeriodStart And dEnd < dPeriodEnd Then dtmTemp = dEnd - dStart ElseIf dStart < dPeriodStart And dEnd < dPeriodEnd Then dtmTemp = dEnd - dPeriodStart ElseIf dStart = dPeriodStart And dEnd = dPeriodEnd Then dtmTemp = dPeriodEnd - dStart Else dtmTemp = dPeriodEnd - dPeriodStart End If PeriodExposure = dtmTemp End Function "Jay" wrote in message ... I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. |
Complicated Time Formula
Read up on the DateDiff() and DateAdd() and DatePart() functions. These will help you extract the needed information. If you need more info, feel free to drop me an email. I am now just finishing the same thing for another client. Fun code !
|
Complicated Time Formula
Jay, Here's my offering:
Put a column of 24 cells somewhere on the worksheet with the values 0 to 23 and name it "Intervals" (I think range names are case sensitive, so be sure your case matches the one in the macro). Name the 2-cell range with the two input cells "TimeInput". Name the 24-cell range adjacent (to the right) to the "Intervals" range "Results". Run this macro for each set of inputs: HTW, Shockley Private arr(1 To 24, 2) As Integer Sub Tester() Range("Results").ClearContents Erase arr BeginTime = Range("TimeInput").Cells(1) EndTime = Range("TimeInput").Cells(2) HourSpan = Hour(EndTime) - Hour(BeginTime) If HourSpan 0 Then arr(1, 1) = Hour(BeginTime) arr(1, 2) = 60 - Minute(BeginTime) arr(2, 1) = Hour(EndTime) arr(2, 2) = Minute(EndTime) Else arr(1, 1) = Hour(BeginTime) arr(1, 2) = Minute(EndTime) - Minute(BeginTime) End If x = 2 If HourSpan 1 Then For i = Hour(BeginTime) + 1 To Hour(EndTime) - 1 x = x + 1 arr(x, 1) = i arr(x, 2) = 60 Next i End If EnterData End Sub Sub EnterData() For i = 1 To 24 If arr(i, 1) = Empty Then Exit Sub Range("Intervals").Find( _ What:=arr(i, 1), _ LookIn:=xlValues) _ .Offset(0, 1) _ = arr(i, 2) Next i End Sub "Jay" wrote in message ... I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. |
Complicated Time Formula
To use the formula below, you will need to do a couple of
things first 1. insert two new rows, the first will contain the start time for an interval, the second will contain the finish time for the interval e.g. below 9-10, the first cell will contain 09:00 the next cell will contain 10:00 2. name the start time cell as StartTime and the finish time as FinishTime now in the cells below these interval starts and finished, add the following formula (assuming this formula will go in a cell in the C column, and that row 2 contains the interval start times, and row 3 contains the interval finish times =IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0, (FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime) *24*60)) The 24*60 converts the numbers from fractions of a day into minutes Kevin Beckham -----Original Message----- I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. . |
Complicated Time Formula
Hi Kevin!
I think you may get an error with (e.g) start time = 9:00 finish time = 9:45 (i.e. start and finish in the same hour slot. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Kevin Beckham" wrote in message ... To use the formula below, you will need to do a couple of things first 1. insert two new rows, the first will contain the start time for an interval, the second will contain the finish time for the interval e.g. below 9-10, the first cell will contain 09:00 the next cell will contain 10:00 2. name the start time cell as StartTime and the finish time as FinishTime now in the cells below these interval starts and finished, add the following formula (assuming this formula will go in a cell in the C column, and that row 2 contains the interval start times, and row 3 contains the interval finish times =IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0, (FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime) *24*60)) The 24*60 converts the numbers from fractions of a day into minutes Kevin Beckham -----Original Message----- I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. . |
Complicated Time Formula
Too much haste, corrected version
=IF(StartTime<D2,IF(FinishTimeD3,60,IF(FinishTime <D2,0, (FinishTime-D2)*24*60)),IF(StartTimeD3,0,IF(FinishTimeD3, (D3-StartTime)*24*60,(FinishTime-StartTime)*24*60))) Kevin Beckham -----Original Message----- Hi Kevin! I think you may get an error with (e.g) start time = 9:00 finish time = 9:45 (i.e. start and finish in the same hour slot. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Kevin Beckham" wrote in message ... To use the formula below, you will need to do a couple of things first 1. insert two new rows, the first will contain the start time for an interval, the second will contain the finish time for the interval e.g. below 9-10, the first cell will contain 09:00 the next cell will contain 10:00 2. name the start time cell as StartTime and the finish time as FinishTime now in the cells below these interval starts and finished, add the following formula (assuming this formula will go in a cell in the C column, and that row 2 contains the interval start times, and row 3 contains the interval finish times =IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0, (FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime) *24*60)) The 24*60 converts the numbers from fractions of a day into minutes Kevin Beckham -----Original Message----- I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. . . |
Complicated Time Formula
|
Complicated Time Formula
Norman,
Thank you very much. This was the type of solution I was hoping for, as I am not very finiliar with VBA. The formula you suggested functions well, however it results are placed into the wrong cell. They are off by one hour. I.E. A2 Start time 12:15 B2 End Time 12:30 In the 11:00 Column 15 comes up. Thank you Very Much -----Original Message----- Hi Jay! Here's a formula approach that looks OK: A2: Start time B2: Stop time C1: 8:00 D1: =C1+1/24 Copied across C2: =IF(OR(HOUR(D1)<HOUR($A$2),HOUR($B$2)<HOUR (D1),$B$2=D1),0,IF(HOUR(D1)= HOUR($A$2),MIN((E1-$A$2),($B$2-$A$2))*24*60,IF(HOUR(D1) =HOUR($B$2),($B $2-D1)*24*60,60))) I've defaulted times outside the range to 0. You may prefer "" It seems to test OK but I'm not so sure that there aren't more efficient approaches. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. . |
Complicated Time Formula
Hi Jay!
We are obviously set up a little different because mine is coming up OK. A2 Start Time B2 End Time C1 8:00 D1 =C1+1/24 Copied across Formula is in C2 copied across. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. wrote in message ... Norman, Thank you very much. This was the type of solution I was hoping for, as I am not very finiliar with VBA. The formula you suggested functions well, however it results are placed into the wrong cell. They are off by one hour. I.E. A2 Start time 12:15 B2 End Time 12:30 In the 11:00 Column 15 comes up. Thank you Very Much |
Complicated Time Formula
I have it set up exactly as you describe and it still
comes up one hour off. Any Idea on how to have it function with time period that pass through midnight. Thank You again Jay -----Original Message----- Hi Jay! We are obviously set up a little different because mine is coming up OK. A2 Start Time B2 End Time C1 8:00 D1 =C1+1/24 Copied across Formula is in C2 copied across. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. wrote in message ... Norman, Thank you very much. This was the type of solution I was hoping for, as I am not very finiliar with VBA. The formula you suggested functions well, however it results are placed into the wrong cell. They are off by one hour. I.E. A2 Start time 12:15 B2 End Time 12:30 In the 11:00 Column 15 comes up. Thank you Very Much . |
Complicated Time Formula
I think you'll find the formula works if you copy the formula to D2, not C2
as he suggested. "Jay" wrote in message ... I have it set up exactly as you describe and it still comes up one hour off. Any Idea on how to have it function with time period that pass through midnight. Thank You again Jay -----Original Message----- Hi Jay! We are obviously set up a little different because mine is coming up OK. A2 Start Time B2 End Time C1 8:00 D1 =C1+1/24 Copied across Formula is in C2 copied across. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. wrote in message ... Norman, Thank you very much. This was the type of solution I was hoping for, as I am not very finiliar with VBA. The formula you suggested functions well, however it results are placed into the wrong cell. They are off by one hour. I.E. A2 Start time 12:15 B2 End Time 12:30 In the 11:00 Column 15 comes up. Thank you Very Much . |
Complicated Time Formula
That did it Thank you Rob
|
Complicated Time Formula
One Problem down, one to go. The formula has trouble with
anytime around midnight. I get a negative number. Any suggestions. Thanks for your help Jay |
Complicated Time Formula
Could you supply the start and end times which are causing problems?
"Jay" wrote in message ... One Problem down, one to go. The formula has trouble with anytime around midnight. I get a negative number. Any suggestions. Thanks for your help Jay |
Complicated Time Formula
After having a very big think about your problem, it's much more complicated
than I first thought. You have a start and end checking time range, call them cS, cE You also have a start and end event time range, call them eS, eE Consider also that an event may start before midnight and end after midnight, but you are not dealing with dates. So it would appear the event finished before it ended. eg. 23:30 to 02:30. I've detected the following combinations in which the times may be ordered, along with the outcome: eS, eE, cS, cE = 0 eE, eS, cS, cE = cE-cS eS, cS, eE, cE = eE-cS eE, cS, eS, cE = cE-eS eS, cS, cE, eE = cE-cS eE, cS, cE, eS = 0 cS, eS, eE, cE = eE-eS cS, eE, eS, cE = eE-cS + cE-eS cS, eS, cE, eE = cE-eS cS, eE, cE, eS = eE-cS cS, cE, eS, eE = 0 cS, cE, eE, eS = cE-cS So your formula for the first line could look like: if eS < eE and eE < cS and cS < cE then 0, else..... There's also opportunity to simplify since some of the outcomes are the same (eg. = 0) Hope this helps, Rob "Jay" wrote in message ... One Problem down, one to go. The formula has trouble with anytime around midnight. I get a negative number. Any suggestions. Thanks for your help Jay |
Complicated Time Formula
Hi Rob!
I've been playing with this for some time and am coming to the same conclusions as you. In my view, the data might need re-arranging so that each day is dealt with separately so as to avoid the problems of spanning midnight. It's not that it can't be done. But the solution is very complicated. May I ask as an aside what is the underlying purpose of the splitting up of the times? It may be that a more simple approach can achieve the same results. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Rob van Gelder" wrote in message ... After having a very big think about your problem, it's much more complicated than I first thought. You have a start and end checking time range, call them cS, cE You also have a start and end event time range, call them eS, eE Consider also that an event may start before midnight and end after midnight, but you are not dealing with dates. So it would appear the event finished before it ended. eg. 23:30 to 02:30. I've detected the following combinations in which the times may be ordered, along with the outcome: eS, eE, cS, cE = 0 eE, eS, cS, cE = cE-cS eS, cS, eE, cE = eE-cS eE, cS, eS, cE = cE-eS eS, cS, cE, eE = cE-cS eE, cS, cE, eS = 0 cS, eS, eE, cE = eE-eS cS, eE, eS, cE = eE-cS + cE-eS cS, eS, cE, eE = cE-eS cS, eE, cE, eS = eE-cS cS, cE, eS, eE = 0 cS, cE, eE, eS = cE-cS So your formula for the first line could look like: if eS < eE and eE < cS and cS < cE then 0, else..... There's also opportunity to simplify since some of the outcomes are the same (eg. = 0) Hope this helps, Rob "Jay" wrote in message ... One Problem down, one to go. The formula has trouble with anytime around midnight. I get a negative number. Any suggestions. Thanks for your help Jay |
Complicated Time Formula
I'm not sure what you mean by splitting up of the times.
I'm taking the checking range (eg. 9:00 to 10:00, or 10:00 to 11:00, etc..) and the event range (eg. 9:15 to 11:30) The goal is to determine how much event time crosses the checking time range. An assumption I've made is that the event will never be equal to or greater than 24 hours. There are certainly optimisations to be made as the formula is implemented. I don't know how I could simplify the approach though. On my scrap piece of paper, I drew two gantt chart looking lines. The top line for the event range, the bottom line for the checking range. I'll try to draw it in ascii. You may need to switch to courier font (or a fixed-width font). These examples are for items 1, 3, 5 and 6 in the list I posted before. 1. =0 eS eE |-------| |---------------| cS cE 3. =eE-cS eS eE |-------| |---------------| cS cE 5. =cE-cS eS eE |-----------------------| |---------------| cS cE 6. =0 eE eS ------| |------ |---------------| cS cE "Norman Harker" wrote in message ... Hi Rob! I've been playing with this for some time and am coming to the same conclusions as you. In my view, the data might need re-arranging so that each day is dealt with separately so as to avoid the problems of spanning midnight. It's not that it can't be done. But the solution is very complicated. May I ask as an aside what is the underlying purpose of the splitting up of the times? It may be that a more simple approach can achieve the same results. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Rob van Gelder" wrote in message ... After having a very big think about your problem, it's much more complicated than I first thought. You have a start and end checking time range, call them cS, cE You also have a start and end event time range, call them eS, eE Consider also that an event may start before midnight and end after midnight, but you are not dealing with dates. So it would appear the event finished before it ended. eg. 23:30 to 02:30. I've detected the following combinations in which the times may be ordered, along with the outcome: eS, eE, cS, cE = 0 eE, eS, cS, cE = cE-cS eS, cS, eE, cE = eE-cS eE, cS, eS, cE = cE-eS eS, cS, cE, eE = cE-cS eE, cS, cE, eS = 0 cS, eS, eE, cE = eE-eS cS, eE, eS, cE = eE-cS + cE-eS cS, eS, cE, eE = cE-eS cS, eE, cE, eS = eE-cS cS, cE, eS, eE = 0 cS, cE, eE, eS = cE-cS So your formula for the first line could look like: if eS < eE and eE < cS and cS < cE then 0, else..... There's also opportunity to simplify since some of the outcomes are the same (eg. = 0) Hope this helps, Rob "Jay" wrote in message ... One Problem down, one to go. The formula has trouble with anytime around midnight. I get a negative number. Any suggestions. Thanks for your help Jay |
Complicated Time Formula
Hi Rob!
"I'm not sure what you mean by splitting up of the times." I was thinking in terms of breaking the times at Midnight We might have a row of times from 00:00 to 23:00 Then break into another row from 00:00 to 23:00 The second row can be conditional on overlapping midnight. That way the formula approach would not need to be adjusted for the case of stop time < start time. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Rob van Gelder" wrote in message ... |
Complicated Time Formula
Wow thank you for all the help.
What my utilmate goal is to determine a utilization ratio for an ambulance service I.e. if an ambulance was on a call from 23:15 to 00:15, I want to be able to show that that ambulance was active for 75% of the 23:00 time block and 25% of the 00:00 time block. This easy to do when dealing with one call, but I am dealing with several thousand over a month long period. Once I am able to determine the amount of time spent on each call in each time block the rest is very easy to complete. Thank you again. You guys have been great. The main hang up is the 00:00 time block. Jay |
Complicated Time Formula
Rob,
Okay. that is quite a few differant cominations. Would it be at all helpfull if I was able to include the dates in the start and end times? I do have that data available and can incorperate it if need be. The question is I do not want to incorperate the dates into the results, as I only need a total time in each time block for my end result. Jay |
Complicated Time Formula
Oh my word yes!!!!!
-- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... Rob, Okay. that is quite a few differant cominations. Would it be at all helpfull if I was able to include the dates in the start and end times? I do have that data available and can incorperate it if need be. The question is I do not want to incorperate the dates into the results, as I only need a total time in each time block for my end result. Jay |
Complicated Time Formula
Suddenly I wish I had said I could do that earlier. Okay
so I can do one of two things. I can write a simple formula to combine the two date and times into one or I can use four cells. i.e. A2=start date B2=start time C2=end date d2=end time I did not mention this earlier because I didn't really feel it would be needed. Sorry. Thank You so much for the time and effort. |
Complicated Time Formula
Hi JAY!
I'll take a look when my toothache goes away enough! But it looks pretty straightforward now thanks to ability to have date added to the time. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "JAY" wrote in message ... Suddenly I wish I had said I could do that earlier. Okay so I can do one of two things. I can write a simple formula to combine the two date and times into one or I can use four cells. i.e. A2=start date B2=start time C2=end date d2=end time I did not mention this earlier because I didn't really feel it would be needed. Sorry. Thank You so much for the time and effort. |
Complicated Time Formula
Hi Norm,
If you were in the states I'd drive you to the dentist. Thank you! Jay |
Complicated Time Formula
Hi Jay!
Send a copy of the workbook you have to me and I'll play about with it. Should be faster that way. Rinsing mouth with Single Malt seems to be quite good for tooth ache. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... Hi Norm, If you were in the states I'd drive you to the dentist. Thank you! Jay |
Complicated Time Formula
Hi Jay!
I think that I've got it working OK now. A1: Start Time B1: Finish Time A2: Named StartTime A2 Sample entry: 30-Dec-2003 8:30 B2: Named FinishTime B2 Sample entry: 30-Dec-2003 12:30 C1: =DATE(YEAR(StartTime),MONTH(StartTime),DAY(StartTi me)) D1: =C1+1/24 Copied across to AZ1 (i.e. covers 2 days + 1 hour) C2: =IF(StartTime<C1,IF(FinishTimeD1,60,IF(FinishTime <C1,0,(FinishTime-C1 )*24*60)),IF(StartTimeD1,0,IF(FinishTimeD1,(D1-StartTime)*24*60,(Fin ishTime-StartTime)*24*60))) Copied across to AY2 (note that the row 1 time overlaps the row 2 calculation by 1 hour (this prevents a problem with the final cell calculation) I've tested for the difficult problems of starting and finishing within the hour and with over-lapping midnight and / or noon and it seems OK I'll send workbook if you want but you can construct from the above. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... Hi Norm, If you were in the states I'd drive you to the dentist. Thank you! Jay |
Complicated Time Formula
Norm,
I am glad you have found the cure for toothaches. Could you send me the workbook. I must be doing something wrong as I am getting errors in every cell from K2 on. My email address is listed below. Also is this going to work if i copy it down to perform this function for several hundred sets if times? Thank you Jay |
Complicated Time Formula
Hi Jay!
Have done! I think it was the Single Malt that cured the toothache. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... Norm, I am glad you have found the cure for toothaches. Could you send me the workbook. I must be doing something wrong as I am getting errors in every cell from K2 on. My email address is listed below. Also is this going to work if i copy it down to perform this function for several hundred sets if times? Thank you Jay |
Complicated Time Formula
Hi Jay!
There will be some amendments need to facilitate copying down. But nothing too serious. Let's get it working once first <vbg -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jay" wrote in message ... Norm, I am glad you have found the cure for toothaches. Could you send me the workbook. I must be doing something wrong as I am getting errors in every cell from K2 on. My email address is listed below. Also is this going to work if i copy it down to perform this function for several hundred sets if times? Thank you Jay |
Complicated Time Formula
No words can describe how greatfull I am. Thank You. Now I
just need to get it working for several hundred rows. The catch is there is going to be a months worth of dates to work off of. I am going to end up summing each column for each time slot. So they would need to be constant. Which causes a problem with the dates in the first row. Jay |
Complicated Time Formula
here i send my offer without macros. Sorry for my english.
HEP Jay wrote: *I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. Attachment filename: problema complejo de horarios.xls Download attachment: http://www.excelforum.com/attachment.php?postid=39367 -- Message posted from http://www.ExcelForum.com |
Complicated Time Formula
Hi Hector!
We got it all working OK off newsgroup in the end. The key formula was: =IF(C1="","",IF($A2<C1,IF($B2D1,60,IF($B2<C1,0,($ B2-C1)*24*60)),IF($A 2D1,0,IF($B2D1,(D1-$A2)*24*60,($B2-$A2)*24*60)))) A2 contained the start date and time and B2 contained the stop date and time. We managed a copy down OK and a summing of the results. The file of 10000 records is a bit big but I've suggested using monthly files and then a summary file. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "HÉCTOR E. POLLA" wrote in message ... here i send my offer without macros. Sorry for my english. HEP Jay wrote: *I have a comlicated formula that I need to develop and I'm looking for some help. I have two cells that contain a start time and an end time. I need to calculate the total time between the two times and place the value into the approipriate time slot. i.e. if the start time is 09:15 and the end time is 11:30 the value in the 09-10 cell would be 45 and the value in the 10-11 cell would be 60, and the value in the 11-12 cell would be 30. Any ideas or hints. Thank you. * Attachment filename: problema complejo de horarios.xls Download attachment: http://www.excelforum.com/attachment.php?postid=393678 --- Message posted from http://www.ExcelForum.com/ |
| All times are GMT +1. The time now is 06:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com