incorrect formulation

[NikolinoDE]

Hello everyone, need a little food for thought ... or better...need your help :)

Examble (Cell L14) :

=ODER(UND(L$9>=$C$3;L$9<=$D$3);UND(L$9>=$E$3;L$9<=$F$3); UND(L$9>=$G$3;L$9<=$H$3))

With this formula I can create a timeline as long as the working time is longer.

If the working hours from 8:00 p.m. to 11:00 p.m. then everything is ok, shows how it should display.

If the working hours are from 10pm to 3am then it shows empty,

how can I correct this error in my formula?

Nikolino

I know I don't know anything (Socrates)

[#2]

@NikolinoDE Hi, I need to do a similar scheduling but I really doesn't understand your formula and what you try do to with this 

=ODER(UND(L$9>=$C$3;L$9<=$D$3);UND(L$9>=$E$3;L$9<=$F$3); UND(L$9>=$G$3;L$9<=$H$3))

Could you explain it with more details ?

[#3]

=OR(AND(L$9>=$C$3,L$9<=$D$3),AND(L$9>=$E$3,L$9<=$F$3), AND(L$9>=$G$3,L$9<=$H$3))

translated the formula into english ... maybe it helps better

[#4]

@NikolinoDE 

As variant

and in attached file.

[#5]

Hello Sergei Baklan,

thx for your quick response.
I open the sheet and try to insert in G 3 and H3 time like 23:00 in G3 and 04:00 in H3 and not works...am i making a mistake somewhere?

Thx
Nikolino

[#6]

@NikolinoDE   I think you are having a problem with the next day roll over.  If you want to treat 03:00am as a continuation from the previous day you have to enter it as a day after the prior 22:00 (if you look at @Sergei Baklan sheet, he entered H3 as 27/24 or 3hrs more than a full day).  If you don't want to have to do that every time and you know that column H will always have a time in the next day you can +1 in the formula or you can change row 7 to make it all the "same day" and use an OR condition >22:00 OR <03:00 as long as you are consistent.  If you don't know that H will always be the "next day" then you need to add additional condition to first check for that.

[#7]

How should the formula look like that you enter the time in H3 and appear in the timeline, even if the end time is less than the start time?
In the end, I would like to enter the times from C3 to H6 (see picture at the beginning) and they should appear automatically in the timeline ... also, as I said, if the end time is less than the start time.

Thx
Nikolino

[#8]

@NikolinoDE  I added the extra condition to @Sergei Baklan sheet (see attached):

=((J$7>=$C4)*(J$7<=$D4)+(J$7>=$E4)*(J$7<=$F4)+(J$7>=$G4)*(J$7<=$H4)+($H4<$G4)*(J$7>=$G4)*(J$7<=$H4+1))

note you could still do all this using AND and OR conditions (AND = * and OR = +)

NOTE: this is only accounting for the case where col H is supposed to be >24hrs day but typed in without the added day.  If you have a chance of other times being typed in 'incorrectly' that way (e.g. col G = 00:30 and col H = 05:30) then you may need another condition for that.

[#9]

@NikolinoDE 

Yes, as variant

In any case you shall define the logic to find if that's the current date or next one. That could be finish date < start date, position in the range, whatever. But all these is not reliable, much better define next day directly. 

[#10]

@NikolinoDE  Here is another variant that might work better.  It is assuming that columns G and H are 3rd shift and always starts after 12:00 noon and ends before 12:00 noon the next day:

=((J$7>=$C3)*(J$7<=$D3)+(J$7>=$E3)*(J$7<=$F3)+(J$7>=$G3+($G3<0.5))*(J$7<=$H3+($H3<0.5)))

[#11]

Many thanks for the help, mtarler & Sergei Baklan.

Will be able to deal with it further tomorrow, if someone thinks of something by then it is very welcome.
In our conversation shows that it is better to change the date after midnight to the next day ... is there a formula where a date is taken from a cell and a fixed time can be in one cell? ..so I could ... just a thought..the day in the fixed time that comes from another cell...or am I in the wrong line of thought ... however, any additional help is welcome ... thank you again and will give you a message about the result.

Thx
Nikolino

[#12]

@NikolinoDE 

Afraid I didn't catch the question. For such schedules usually actual date is not required, that's only to add 1 to separate the next date form the current one.

In general datetime=date+time

[#13]

@NikolinoDE  If you want to encourage or force entry to properly include (or not include) the additional day you can use conditional formatting (encourage by highlighting 'wrong' values) or data validation which can either give warning message or error message.  Basically the condition will be that the value entered >= the value in the column before it (or no value can be >1 to not include the additional day).  That formatting/validation could be applied to that whole table. 

The last solution I sent should work for any 3rd shift time range entered as either same date or next day values (i.e. times in columns G and H should be evening of same day or morning hours the next day).

[#14]

@NikolinoDE 

Hi Guys,
I tried again to adapt your advice in a workbook ... but somewhere I'm messing up.

I installed the Fromel in sheet "IND1" and MA1 ... probably wrong  :( ...
But what I want is that the shifts start from 6:00 am until the other day 6:00 am in a row as a timeline,

the time is taken from the timeline of sheet "Januar",

how could I do something with conditional formatting ?

Also attached is the worksheet
Hope I could explain well what I would like to do..

sry for the circumstances ... but as a VBA blind and Excel ignorant I want to do it somehow (somehow GOOD), the ambition grabbed me :-).

Every idea is welcome ... I'll sit down with it again tomorrow ... now my grandchild is ahead (to be visited) :)

Thx

Nikolino

I always know that I don't know anything ... when do I know when I know something?

[#15 · Accepted solution]

@NikolinoDE  I didn't see any change in your conditional formatting formula.  I implemented the last suggestion I made in the attached sheet and added more so it will check what day it is and auto-index down on the Jan sheet so 1 conditional formatting formula will work for every day in that month (I added the 2nd to the range and leave it to you to add the rest).  You still need to tweak the formula for each category (MA1, MA2, etc...) since each needs its own color.  

I also changed the formula to >= the start time but < end time because the don't actually work that hour/ half-hour period.

I also noted and corrected that your 1st 06:00am was actually 06:00am the next day and your 06:00am at the end was 05:59.999999999am (floating point error).  You may want to look at the rest of the times and days for those and similar errors also.

[#16]

A big thank you to mtarler and Sergei Baklan.
Thank you for the food for thought ... is in the direction I imagined.
When it is finished I will put it here so that everyone who needs something can download it
... it will help to help :)

Wish everyone a wonderful good morning ... let's enjoy the day :)

Nikolino
I know I don't know anything (Socrates)

[#17]

@NikolinoDE  glad it helped.  BTW, love your signature quote.