Splitting up timeperiod

B

Bret Bernever

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
 
S

SteveM

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

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
 
B

Bret Bernever

SteveM schreef:
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
Click to expand...

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
 
S

SteveM

SteveMschreef:







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

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
 
M

Max

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
 
B

Bret Bernever

On Thu, 10 Jan 2008 05:56:31 +0800, Max wrote...
Post a link to your sample.
You could use:
http://www.freefilehosting.net/
Click to expand...

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

************************
 
B

Bret Bernever

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

Whawwwwww, that looks pretty cool! You made someone very happy :eek:)) 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.
 
M

Michelle Mok

Hi, I'm interested in the same thing with what Bret was trying to achieve with excel. I noted you have come about with a formula for it. Could you share?

I have attempted to access the files on the shared hosting site but I think they have already been taken down since its already been 2 years since this post.
 

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

Problem formatting dates from CSV file 2
15 minute data to daily data 1
Determine if night shift by start & end time 2
Averaging by half hour time intervals. 0
I need Time Range to return Value 4
calculating average handling time 7
Need help 3
Conditional Time Calculation 10

Top