## IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then calc

### IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then calc

Hi People.
Im trying to have a formula check for being BLANK and for a TEXT string (easy part), then if none are present then check that the time difference between 2 cells is less than 8 hours (thats where Im up to now with the below formula....)
What I need the new formulas to do is:

1 - not only check that the difference is less than 8 hours, but also verify the timing of the shift (EARLY SHIFT i.e. employee half day leaving early or half day beginning late) and return 0,5 for TRUE (which is why it divides by 2 at the end representing the hlaf day) and 0 for FALSE.
2 - not only check that the difference is less than 8 hours, but also verify the timing of the shift (LATE SHIFT i.e. employee half day leaving early or half day beginning late) and return 0,5 for TRUE (which is why it divides by 2 at the end representing the hlaf day) and 0 for FALSE.

So I will have results cells for:
1 - half days early (late or early leaving)
2 - half days late (late or early leaving)

So the cells containing the times could have any of the following (see image):
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
20:30
21:00
21:30
Vacation
Moving home
Sick
Absent (no info)

This is what I have until now...
=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))

Im even wondering if this is going to be possible.
Any help is appreciated.
Cheers.
Attachments
Working formula
Cells with times of start and finish
IF NEW 2.JPG (9.02 KiB) Viewed 317 times
OpenOffice 3.1 on Windows Vista
pherriot

Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

### Re: IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then c

This seems the same as https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=97568&p=467954#p467954, which is marked as Solved. I will lock this thread and look for any further discussion, or an explanation of what is different, in the other thread.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm

### Re: IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then c

After a PM from pherriot, I unlocked the thread.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm

### Re: IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then c

Hi,
what does "early leaving" (resp. "late beginning") mean exactly?
Last edited by mikele on Thu Apr 04, 2019 9:49 am, edited 1 time in total.
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele

Posts: 59
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

### Re: IF - ISBLANK - ISTEXT - A1 and A2 less than 08:00 then c

Hi Mikele.
Simply put it means an Agent can have one of 2 shift patterns, and if he requires a half day then any of the following combinations could apply:
1 - Early shift (07:45) finishes early (12:00)
2 - Early shift (07:45) starts late (12:00) and finishes on time (16:00)
3 - Late shift (10:45) finishes early (15:00)
4 - Late shift (10:45) starts late (15:00) and finishes on time (19:00)
...thanks for looking....
OpenOffice 3.1 on Windows Vista
pherriot

Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm