[Solved] Need Conditional formatting and statement help

Discuss the spreadsheet application
Post Reply
swdynamic
Posts: 4
Joined: Thu May 25, 2017 5:52 pm

[Solved] Need Conditional formatting and statement help

Post by swdynamic »

Trying to create employee schedule using start and end times. Need to set conditional formatting to fill row based on column time data (employee scheduling / Gantt chart type) only explanations I have found close are for Excel and formulas don't quite work.
Formula I am trying as follows...
=and($E2>=$B4,$F2<$C4) with format fill blue if "true"
Row 2 (E2 6:00 am) is time at 6:00 / 6:15 / 6:45 etc and using B4= start time "6:00 am", C4= end time with F2= 6:15 am etc through 10:00 pm in 15 min intervals.

Is either giving #ref errors or fills entire set of rows. Need to get it to show shift coverage like Bar Gantt graph

Any help would be greatly appreciated.
Last edited by Hagar Delest on Sun May 28, 2017 9:23 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.4.1
Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need Conditional formatting and statement help

Post by Zizi64 »

Please upload your example .ods file with your formulas and CF settings.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
swdynamic
Posts: 4
Joined: Thu May 25, 2017 5:52 pm

Re: Need Conditional formatting and statement help

Post by swdynamic »

custom work scheduler.ods
(18.73 KiB) Downloaded 65 times
I had several other conditions I had previously set but had to disable or delete before I could get anything else to work. I would like to return to having the Data Validation (LIST) for both start and stop times, but first things first.

Just FYI, I formatted the background color independent from conditional.... simply to make sure view was right, not from formula.You can return it to no fill if needed.

Thanks in advance for the assistance.
OpenOffice 3.4.1
Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need Conditional formatting and statement help

Post by RusselB »

I was unable to get the Conditional Formatting to work as I would expect, but one error you have in your formula is the usage of the , as the parameter separator. OpenOffice uses the ;
I did get the Validity to work for your start and end times. I also made an alteration to your formula calculating the total time of the shift.
See row 4 of the attachment for details as to what I got to work (as well as my attempt on the conditional formatting)
Attachments
custom work scheduler 2.ods
(18.73 KiB) Downloaded 97 times
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
swdynamic
Posts: 4
Joined: Thu May 25, 2017 5:52 pm

Re: Need Conditional formatting and statement help

Post by swdynamic »

WOW! Thanks, with a little copy paste it works throughout, however the background cell fill is coming up short by one cell... any suggestions? ie: if put 6am-2pm it stops shading at 1:45 pm...
OpenOffice 3.4.1
Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Need Conditional formatting and statement help

Post by RusselB »

Probably because the formula checks for greater than or equal to for the start time, but only less than for the end time. Try modifying the formula to include the = for the end time
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
swdynamic
Posts: 4
Joined: Thu May 25, 2017 5:52 pm

Re: Need Conditional formatting and statement help

Post by swdynamic »

Great! thanks
OpenOffice 3.4.1
Windows 10
Post Reply