IF for Interrogating cells for timings?

[pherriot]

Hi everyone.
Im trying to interrogate employee start and finish times (start cell and a finish cell) to return a result of half day worked in any given shift (only 2 different shifts avalaible - 07:45 Early morning shift and 10:45 Late morning shift).
These cells can have the following possible values due to half days leaving early or starting late and attendance issues:

07:45 10:45 12:00 12:30 13:00 13:30 14:00 14:30 15:00 15:30 16:00 16:30 17:00 17:30 18:00 18:30 19:00 19:30 20:00 20:30 21:00 21:30 Business trip - Vacation - Moving home - Sick Absent (no info)

...Im drawn to IF statements.
For example interrogating cell Wk 1'.$C$21 and Wk 1'.$D$21to ensure they have left before their 8 hour shift, and if so return a half day denoted by "0,5":

IF A1 "07:45" + B1 "10:45","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30";"0,5"

...but thats seems very inflexible.
Especially as the potential half day for the employee could have any one of four variations although this forum only allows uploading 3? (see images 1 - 3)....image 4 was late shift finish on time.
Currently I am simly checking the time is less than 8 hrs between cells indicating a half day, but this doesnt tell me what shift they were on so that I know where my resources are and at what time. This is what I have:
=IF(ISBLANK($'Wk 1'.$C$21);"";IF(ISTEXT($'Wk 1'.$C$21);$'Wk 1'.$C$21;($'Wk 1'.$D$21-$'Wk 1'.$C$21<8/24)/2))

Any and all help is appreciated.

Cheers P.
I have another threads in the forum seeking help, but after speaking to the Admin guys, I am posting a reformatted one as I wasnt clear enough.
Other thread:
viewtopic.php?f=9&t=97581

[keme]

The given information is limited, so my advice may be missing the point...

Assuming that there will be only one start and one finish for each cell range, I'd look at using MATCH() on the Agent start/Agent finish column, then use the returned row number with INDEX() (or possibly OFFSET() ) to fetch time of day.

With a rearranged table, having start/finish to the left of times, you could use VLOOKUP() to fetch the start/finish times in a single operation, but that might give trouble for other operations.

Note that you need to use zero in the optional last parameter of MATCH().

Also, using a single cell with data validation to provide a dropdown list should give less clutter than using a full list of time spots, but then you lose the visual cues for overlapping of shifts which may be important.

[pherriot]

Hi Keme.
Thanks.
Yeah, Im finding Im not great at explaining this very well.
Its nothing more than an attempt to provide an easy to look at Rota/Hours display for management:

Part of that is showing how many Agents are on a half day (for that same day).
I guess the real issue is that I MUST have that information extracted from a START cell and FINISH time cell....I need some way of determining if the Agent has had a half day on an early shift or a half day on a late shift....and because he could have came late to work on an early shift (or left early) and conversely on a late shift the same (late start or early finish) then I can see no other way than having some sort of table with values that are then returned if a start and finish time are hit.
The cell containing the formula exists in the Agents own month sheet and is read from there into the days totals (total Agents out on half day earlies or lates).

You are dead on with your comments, visual cue for shift overlap is important to me.

....Im off looking for examples on how to implement your comments regarding MATCH() INDEX() OFFSET().
Any help is appreciated as I am a beginner.
Cheers.
P.

[pherriot]

Ive now created the table (Image 1) with all 4 possibilities (with explanation in 4th column for this forum.)

If I understand this correctly, I am trying to INDEX the table in Image 1, and MATCH the cells in Wk 1'.C21 + Wk 1'.D21 for "07:45" + "12:00" (Image 2) respectively. Then return the result to the cell holding the formula (Image 3). And so the formula I have is.....
=INDEX($'Menus + Emp List'.C71:D74,MATCH(EHLS,;$'Wk 1'.C21:D21; 0))

However all I get is an error....."Err:501"

I havent even got to the OFFSET command yet......Any ideas?

[robleyd]

It is hard for anyone to try and assist you based on images of your spreadsheet - that is like taking a picture of your car to the automotive technician and expecting him/her to diagnose the problem you are having with your car.

Please upload a sample spreadsheet with actual data (disguise any confidential info) and perhaps examples of what you are trying to achieve. I doubt many of the helpers here are prepared to create a spreadsheet based on the limited information available in the images you have provided.

[pherriot]

Thanks for the advice Robleyd
Im on it......

[pherriot]

Hi again.
OK heres the example.

Sheet 7 = Agent hours
Sheet 3 = Agent month view which contains the formula to ascertain the shift pattern and if a half day has been taken (starting late or leaving early)
Sheet 4 = The table for INDEX and MATCH
Menus = For the drop down menu choices in Sheet 7

Expected result in Sheet 3 Cell B8 = 0,5.....this indicates a half day has been taken, as the Agent came at 07:45 and left at 12:00 (see Sheet 7 Cells C2 and C3).
Still gives the error

=INDEX($Sheet4.A2:D5,MATCH(ehls,;$Sheet7.C2:D2; 0))

What am I doing wrong here?
Cheers.
P

[Villeroy]

The data layout, the whole approach is inadequate, far too complicated. Software does not work like this. You are free to organize these spreadsheets analogue to paper sheets, but then you can not expect that any software is able to make sense of it.

[pherriot]

Hi Villeroy

That much I know and thats why I am here trying to solve it in this great forum.

Do you mean the approach is wrong with regard to the formula or the entire theory of calculating in this way? I cant imagine that this is too complex for a spreadsheet....

Remember I was simply attempting to utilize the comments made in this thread from "keme" using MATCH() INDEX() OFFSET()

Any help is appreciated Ladies and Gentlemen.
Cheers.
P.

[Villeroy]

This is how computer programs process lists of things, task, items, list of whatever
2019-04-08 8:30 17:00 Paul
2019-04-08 8:00 17:30 John

There is one column of dates and dates only.
There are two columns of times and times only.
There is one column of text and text only.

Each row records one distinct item, let's say a work shift.
Each column stores one distinct property of each shift, the dates, the start times, the end times, the name in this example.
A table should not store any non-information as the half-hour steps with no info on your sheet but it is possible to generate such a table from my flat list at any time so you can print it on paper and pin it to the office door. But this free hand list is not the source of information.
From a flat list (also called a "normalized table") any database program can derive all kinds of information about the work shifts Calc is NOT a database program, nevertheless it can sort this list by any field, filter the list by any combination of attribute values, lookup stuff and it can generate a pivot table with aggregated hours in which day, month, quarter, week, year for each name.

The info about missing work hours can be stored in a separate normalized table:
2019-03-12 2019-03-20 Peter illness

There is a column of start dates, a column of end dates, a column of names and another text column with descriptions about the incident.

[Villeroy]

Just a demo about what can be achieved within minutes once you start using normalized lists.