Jul 20 2020 03:27 AM
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)
Jul 20 2020 03:43 AM
@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 ?
Jul 20 2020 04:13 AM
Jul 20 2020 04:53 AM
Jul 20 2020 05:00 AM
Jul 20 2020 05:26 AM - edited Jul 20 2020 05:32 AM
@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.
Jul 20 2020 05:43 AM
Jul 20 2020 06:03 AM - edited Jul 20 2020 06:08 AM
@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.
Jul 20 2020 06:17 AM
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.
Jul 20 2020 06:49 AM
@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)))
Jul 20 2020 08:03 AM
Jul 20 2020 08:52 AM
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
Jul 20 2020 09:03 AM
@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).
Jul 20 2020 11:59 AM
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
Jul 20 2020 01:01 PM
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.
Jul 21 2020 12:13 AM
Jul 21 2020 05:42 AM
@NikolinoDE glad it helped. BTW, love your signature quote.
Jul 20 2020 01:01 PM
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.