Forum Discussion

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jul 20, 2020

incorrect formulation

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)

 

  • mtarler's avatar
    mtarler
    Jul 20, 2020

    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.

     

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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?
    • mtarler's avatar
      mtarler
      Silver Contributor

      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.

       

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        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)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    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
    • mtarler's avatar
      mtarler
      Silver Contributor

      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).

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      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

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      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
      • mtarler's avatar
        mtarler
        Silver Contributor

        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 SergeiBaklan 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.

  • ninacomets's avatar
    ninacomets
    Copper Contributor

    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 ?

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      =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

Resources