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

Discuss the spreadsheet application

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

Postby pherriot » Tue Apr 02, 2019 1:17 pm

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
Business trip
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
IF NEW 1.JPG
Working formula
IF NEW 2.JPG
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

Postby FJCC » Tue Apr 02, 2019 2:57 pm

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby FJCC » Tue Apr 02, 2019 4:18 pm

After a PM from pherriot, I unlocked the thread.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby mikele » Tue Apr 02, 2019 8:55 pm

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

Postby pherriot » Wed Apr 03, 2019 9:05 am

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


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 42 guests