Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming;,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
I have 4 Timeslots:
TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 I have one time period: TimePeriod - 17:00-1:00 Question: I want to split the time period in the appropriate timeslots so that the results should be: 17:00-18:00 - Falls in TimeSlot 2 18:00-22:00 - Falls in Time Slot 3 22:00-1:00 - Falls in TimeSlot 4 Any ideas how to accomplish that? I'm interested in the explicit timeintervals: 17:00-18:00 18:00-22:00 22:00-1:00 not the hours count! Thanks in Advance Bret Bernever |
#2
Posted to microsoft.public.excel.programming, microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
On Jan 7, 6:04 pm, Bret Bernever wrote:
I have 4 Timeslots: TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 I have one time period: TimePeriod - 17:00-1:00 Question: I want to split the time period in the appropriate timeslots so that the results should be: 17:00-18:00 - Falls in TimeSlot 2 18:00-22:00 - Falls in Time Slot 3 22:00-1:00 - Falls in TimeSlot 4 Any ideas how to accomplish that? I'm interested in the explicit timeintervals: 17:00-18:00 18:00-22:00 22:00-1:00 not the hours count! Thanks in Advance Bret Bernever Bret, The easiest thing would be to set up a simple VLookUp table like this: 0 4 6 1 8 2 18 3 22 4 The lookup value are the hourly slots. Set the range lookup of the function to TRUE Use HOURS(TimePeriod) as the lookup value for each time entry in your data set. That will associate each Time Period with the appropriate Time Slot. Then you can do what you intend with the info. SteveM |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
SteveM schreef:
On Jan 7, 6:04 pm, Bret Bernever wrote: I have 4 Timeslots: TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 I have one time period: TimePeriod - 17:00-1:00 Question: I want to split the time period in the appropriate timeslots so that the results should be: 17:00-18:00 - Falls in TimeSlot 2 18:00-22:00 - Falls in Time Slot 3 22:00-1:00 - Falls in TimeSlot 4 Any ideas how to accomplish that? I'm interested in the explicit timeintervals: 17:00-18:00 18:00-22:00 22:00-1:00 not the hours count! Thanks in Advance Bret Bernever Bret, The easiest thing would be to set up a simple VLookUp table like this: 0 4 6 1 8 2 18 3 22 4 The lookup value are the hourly slots. Set the range lookup of the function to TRUE Use HOURS(TimePeriod) as the lookup value for each time entry in your data set. That will associate each Time Period with the appropriate Time Slot. Then you can do what you intend with the info. SteveM Thanks Steve for cooperating on this. I think the VLookup function can be part of the solution but can you be more specific on the example and how to set it up. How can the function produce the end result which looks something like this: 17:00-18:00 18:00-22:00 22:00-1:00 I really appriciate your help on this, thanks in advance Bret |
#4
Posted to microsoft.public.excel.programming, microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
On Jan 8, 6:13 am, Bret Bernever wrote:
SteveMschreef: On Jan 7, 6:04 pm, Bret Bernever wrote: I have 4 Timeslots: TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 I have one time period: TimePeriod - 17:00-1:00 Question: I want to split the time period in the appropriate timeslots so that the results should be: 17:00-18:00 - Falls in TimeSlot 2 18:00-22:00 - Falls in Time Slot 3 22:00-1:00 - Falls in TimeSlot 4 Any ideas how to accomplish that? I'm interested in the explicit timeintervals: 17:00-18:00 18:00-22:00 22:00-1:00 not the hours count! Thanks in Advance Bret Bernever Bret, The easiest thing would be to set up a simple VLookUp table like this: 0 4 6 1 8 2 18 3 22 4 The lookup value are the hourly slots. Set the range lookup of the function to TRUE Use HOURS(TimePeriod) as the lookup value for each time entry in your data set. That will associate each Time Period with the appropriate Time Slot. Then you can do what you intend with the info. SteveM Thanks Steve for cooperating on this. I think the VLookup function can be part of the solution but can you be more specific on the example and how to set it up. How can the function produce the end result which looks something like this: 17:00-18:00 18:00-22:00 22:00-1:00 I really appriciate your help on this, thanks in advance Bret Bret it's not clear to me which is the input data. The text tells me its what I think are specific times within each Time Period that you want to associate to the proper Time Slot Your last email above suggests that Time Periods are the outputs. You got me confused. My solution addresses the problem interpreted the first way. Say you have a column of Time Period times. Then use the Hour() and VLookUp functions in an adjacent row with the Time Period time as the reference value and my VLookUp table as the table source. The VLookUp True parameter will search for the nearest Hour() value less than the next increment and then return the Time Slot number. Say a sample data point is 14:36 then Hour(14:36) returns 14, that value delivered to the lookup table returns (Time Slot) 2 because the 14 is greater than or equal to the #2 Slot minimum 8 and strictly less that the maximum 18. You can use the real period values as strings in the return column of the lookup table if you want. You can then use CountIf or something on the Time Slot column just created to generate Slot centric statistics or whatever. SteveM |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
Just some thoughts, riding on your orig. post,
illustrated in this sample: http://www.freefilehosting.net/download/3a6id Denoting TimePeriods.xls This assumes that you have as text inputs in A2 down, eg from your orig. post: 17:00-18:00 18:00-22:00 22:00-1:00 where the end times would always fall within the particular timeslot (a key assumption) If so, the start times in the text inputs could be extracted as numbers using LEFT(..)+0, and the numbers then used as lookup values to return the corresponding timeslot indications in a vlookup In B2, copied down: =IF(A2="","",VLOOKUP(LEFT(A2,SEARCH(":",A2)-1)+0,{0,"22:00-6:00";6,"6:00-8:00";8,"8:00-18:00";18,"18:00-22:00";22,"22:00-6:00"},2)) Or, if you want the results as text, eg: Falls in TimeSlot 4 (as hinted in your orig. post) In C2, copied down: =IF(A2="","",VLOOKUP(LEFT(A2,SEARCH(":",A2)-1)+0,{0,"Falls in TimeSlot 4";6,"Falls in TimeSlot 1";8,"Falls in TimeSlot 2";18,"Falls in TimeSlot 3";22,"Falls in TimeSlot 4"},2)) where TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
On Tue, 8 Jan 2008 16:06:35 -0800 (PST), SteveM wrote...
Bret it's not clear to me which is the input data. Well, now it's getting interesting. You both guys (Steve, Max) point me out in the right direction. But thanks to my awkward explanation i got you confused ;-(( Sorry for that. The solution however is near but not right on the nail! The input data is the starttime and finishtime of the timeperiod (that's just a workshift) for example StTime 17:00 FiTime 01:00 Now, the other data available are the slottimes: TimeSlot 1 - 6:00-8:00 TimeSlot 2 - 8:00-18:00 TimeSlot 3 - 18:00-22:00 TimeSlot 4 - 22:00-6:00 These are just periods when working unsocial hours. Now I need to determine WHAT PART of the time period (the shift) falls in whatever timeslot. The part should be the result. For clearity: The INPUT Max uses in his sample Denoting TimePeriods.xls should be the RESULT (the output) i'm looking for. Conclusion, I get the point of your examples but it doesn't give me the result i'm looking for. But as mentioned earlier that's the result of my bad explanation of the problem :o)) Really appreciate your help and i think we can tackle this pesky problem. -- With kind Regards, Bret Bernever |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
|
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
In article ,
says... Post a link to your sample. http://www.freefilehosting.net/download/3a7cj wkr, Bret |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
On Thu, 10 Jan 2008 05:56:31 +0800, Max wrote...
Post a link to your sample. You could use: http://www.freefilehosting.net/ OK, I got some kind of a solution which works for the moment. But it could only be done in VBA. Posted another sample: http://www.freefilehosting.net/download/3a88g But for clearity i will post the full code here in case somebody is interested. ************************ Sub SplitTime() 'works only with quarter, half or whole hours Dim TmpTime, FiTime, x, StTime, TmpTimeSlot, RaiseFactor, TimeSlot 'clear output area Range("I3:Z3").ClearContents 'the temporary starttime. 'temp, because its manipulated during the course TmpTime = Range("F3") 'the absolute finishtime FiTime = Range("G3") 'this is just for navigating in the sheet 'with the "offset" method x = 3 'if temporary starttime is higher 'then the finishtime - exit the loop Do Until TmpTime = FiTime 'stTime becomes the real starttime StTime = TmpTime 'looks up the timeslot TmpTimeSlot = Application.VLookup(TmpTime, Range("B3:D99"), 2, True) 'when both timeslots (tmpTimeSlot and TimeSlot) 'doen't match - exit the loop. Do 'raise tmpTime with a quarter. The problem here is 'that excel isn't very accurate. The difference 'between 0:30 and 0:15 = 0,0104166666666666 but 'between 0:45 and 0:30 = 0,0104166666666667 and 'sometimes difference = 0,0104166666666661 so I 'have to look up the right value. RaiseFactor = Application.VLookup(TmpTime, Range("B3:D99"), 3, True) TmpTime = TmpTime + RaiseFactor TimeSlot = Application.VLookup(TmpTime, Range("B3:D99"), 2, True) If TmpTime = FiTime Then Exit Do Loop Until TmpTimeSlot < TimeSlot 'Zero means no unsocial hours - nothing to do If TmpTimeSlot = 0 Then Range("F3").Offset(0, x).Value = "-" Range("F3").Offset(0, x + 1).Value = "-" x = x + 2 'else - give me the unsocial hours (THE RESULT i'm looking for) Else Range("F3").Offset(0, x).Value = CDate(StTime) Range("F3").Offset(0, x + 1).Value = CDate(TmpTime) x = x + 2 End If Loop ' To do list: ' search solution when shift spans midnight ' incorporate more flexibility ' finetune because sometimes i get some ' unpredictical results End Sub ************************ -- With kind Regards, Bret Bernever |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
Try this formulas play:
http://www.freefilehosting.net/download/3a8a4 Splitting into TimeSlots.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bret Bernever" wrote in message ... In article , says... Post a link to your sample. http://www.freefilehosting.net/download/3a7cj wkr, Bret |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
Brett,
Try this formulas play: http://www.freefilehosting.net/download/3a8a4 Splitting into TimeSlots.xls Moderately tested here, seems to work fine -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
On Fri, 11 Jan 2008 09:14:44 +0800, Max wrote...
Brett, Try this formulas play: http://www.freefilehosting.net/download/3a8a4 Splitting into TimeSlots.xls Moderately tested here, seems to work fine Whawwwwww, that looks pretty cool! You made someone very happy :o)) This will get me going. What I really like about your solution is that you managed to do it without vba and using just one sheet. This one can be marked as solved. Thank you for providing that solution. Also nice pointing to the http://www.freefilehosting.net/ website. Didn't know that site. Comes in very handy. -- With kind Regards, Bret Bernever |
#13
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
Splitting up timeperiod
welcome, Bret. glad it worked for you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bret Bernever" wrote Whawwwwww, that looks pretty cool! You made someone very happy :o)) This will get me going. What I really like about your solution is that you managed to do it without vba and using just one sheet. This one can be marked as solved. Thank you for providing that solution. Also nice pointing to the http://www.freefilehosting.net/ website. Didn't know that site. Comes in very handy. -- With kind Regards, Bret Bernever |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting a cell | New Users to Excel | |||
splitting Last Name, First Name | Excel Discussion (Misc queries) | |||
Splitting names | Excel Discussion (Misc queries) | |||
Splitting Panes | Excel Discussion (Misc queries) | |||
Splitting Rows | New Users to Excel |