Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 36

Thread: time value format
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    I'm still using that book1 with link, can you see the updated version?
    http://kel.ddns.net/f/a4c3596705/?raw=1
    thanks

  2. #22
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Let me try later, thanks in advance

  3. #23
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    I don't see any issue in the file you sent ...!

  4. #24
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Quote Originally Posted by PCL View Post
    I don't see any issue in the file you sent ...!
    how about this? 20:44 will also colored

  5. #25
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    But don't you ask to color cells when the time value is greater than the time value in A1 ...??

    A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1
    Could show exactly the result you want!
    Last edited by PCL; Oct 21st, 2019 at 08:28 AM.

  6. #26
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    If you don't want the cell in column B to be colored when value is the same as A1
    use
    =IF(TRUNC((B1-INT(B1)),3)>TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)

  7. #27
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Quote Originally Posted by Misca View Post
    Oopsie: Forgot to change the formula to US settings. I'm using the Finnish ( / European ?) version where the delimiter between the parameters is ";". Replace them with commas and it should work:

    =ROUND($A$1-INT($A$1),11)<=ROUND($B1-INT($B1),11)


    I'm using 11 decimals here. That's a fraction of a second so in most of the cases something like 5 or 6 would be close enough (=roughly one second).
    i've tested this one, great like PCL, but i still dont get it, why it doesnt work even "=" added in the formula when i enter the time which is column B has it also
    see B7, B12


  8. #28
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Quote Originally Posted by PCL View Post
    If you don't want the cell in column B to be colored when value is the same as A1
    use
    =IF(TRUNC((B1-INT(B1)),3)>TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)
    i want it same when i enter the time in A1, so i use ">="
    however, when A1 is lesser than B, still colored. confusing and mess you all, i'm so sorry
    PS: B7, B12 still color if A1 is 20:29


  9. #29
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,317
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    I'm lost
    however, when A1 is lesser than B, still colored. confusing and mess you all, i'm so sorry

    Yes, because B will be colored when B'values are greated than A1

    PS: B7, B12 still color if A1 is 20:29
    Yes,
    B7 = B12 = 20:30 which is greater than A1
    Where is it wrong ???

  10. #30
    Board Regular
    Join Date
    Mar 2015
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Quote Originally Posted by PCL View Post
    I'm lost

    Yes, because B will be colored when B'values are greated than A1
    [/COLOR]

    Yes,
    B7 = B12 = 20:30 which is greater than A1
    Where is it wrong ???
    i messed obviously is 20:31, not 20:29
    i feel sorry, anyway thanks in advance PCL

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •