3 Replies Latest reply: Feb 6, 2015 1:13 PM by Rob Wunderlich RSS

    time(now()) problem

    Meitar Goldenberg

      Someone knows why this syntax doesn't work?

       

      =if(time(Now())>= '24:00:00' and time(Now())< '12:00:00','Good Mornning',

      if(time(Now())>='12:00:00' and time(Now())< '18:00:00','Good after Noon',

      if(time(Now())>='18:00:00' and time(Now())< '20:00:00','Good Evening',

      if(time(Now())>='20:00:00' and time(Now())< '24:00:00','Good Night'))))

        • Re: time(now()) problem
          Bill Markham

          Try something like this :

           

          =if(frac(Now())>= 0 and frac(Now())< 12/24 ,'Good Morning',

          if(frac(Now())>=12/24 and frac(Now())< 18/24,'Good Afternoon',

          if(frac(Now())>=18/24 and frac(Now())< 20/24,'Good Evening',

          if(frac(Now())>=20/24 and frac(Now())< 24/24,'Good Night'))))

          • Re: time(now()) problem
            Frank Hartmann

            you have to combine with date function:

             

            =if(date#(time(Now()))>= '24:00:00' and date#(time(Now()))< '12:00:00','Good Mornning',

            if(date#(time(Now()))>='12:00:00' and date#(time(Now()))< '18:00:00','Good after Noon',

            if(date#(time(Now()))>='18:00:00' and date#(time(Now()))< '20:00:00','Good Evening',

            if(date#(time(Now()))>='20:00:00' and date#(time(Now()))< '24:00:00','Good Night'))))

            • Re: time(now()) problem
              Rob Wunderlich

              Before I answer your "why doesn't this work" question, let me first offer an alternative way to write the expression:

               

              =if(Hour(Now())< 12,'Good Mornning'

              ,if(Hour(Now())< 18,'Good after Noon'

              ,if(Hour(Now())< 20,'Good Evening'

              ,'Good Night'

              )))

               

              As to why your syntax doesn't work. You are trying to do a string comparison of just the time portion. The time() function assigns a display format, it does not change the underlying internal value. The comparison would still use the full timestamp (Date + Time).

               

              If you wanted a formatted time as a string, add the text() function.

              if(text(time(now(),'hh:mm:ss'))< '12:00:00', 'Good Morning')

               

              But better to do a numeric comparison as in my example or Bill's suggestion.

               

              -Rob

              http://masterssummit.com

              http://robwunderlich.com