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