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

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

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

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

Post a link to your sample.
You could use:
http://www.freefilehosting.net/

In article ,
says...

Post a link to your sample.

http://www.freefilehosting.net/download/3a7cj

wkr,
Bret

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