Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

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

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

    Default time value format

    hi all, i would like to ask how can i amend my issue.

    A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1
    i've manual changed the format both are "hh:mm", while i always get stuck in column B with format "date time" like "8/10/2019 20:15", thus i cant get the answer.

    thanks all

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

    Default Re: time value format

    Select B1
    Use next formula for Conditional Format
    =IF(B1>$A$1,TRUE,FALSE)
    Copy B1 and special paste to B1:B1OO

  3. #3
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,520
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    Try something like

    =MOD($B1,INT($B1))<$A$1 ( or simply =($B1-INT($B1))<$A$1 )

    The MOD returns the reminder ( = time ). It takes two parameters, number and divisor. INT returns only the whole number so I'm using it as the divisor. Now the MOD only returns the time value without the date part.

    The second suggestion does exactly the same by subtracting the whole number from the decimal one.

  4. #4
    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
    Select B1
    Use next formula for Conditional Format
    =IF(B1>$A$1,TRUE,FALSE)
    Copy B1 and special paste to B1:B1OO
    i tried this in conditional formatting, it doesn't work? time format issue?

  5. #5
    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
    Try something like

    =MOD($B1,INT($B1))<$A$1 ( or simply =($B1-INT($B1))<$A$1 )

    The MOD returns the reminder ( = time ). It takes two parameters, number and divisor. INT returns only the whole number so I'm using it as the divisor. Now the MOD only returns the time value without the date part.

    The second suggestion does exactly the same by subtracting the whole number from the decimal one.
    which cell should i use this formula?

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Location
    Southern Finland
    Posts
    1,520
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: time value format

    I understood you were looking for the formula to use with the conditional formatting.

    But if you only want to get the time part of the datetime value to your worksheet use the =$B1-INT($B1) where you want to have the time.

    The formulas expect there's just time value in A1 and the datetime in the B-column. If this is not the case you might want to use the same formula for A1 in the conditional formatting equation.
    Last edited by Misca; Oct 20th, 2019 at 07:43 AM.

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

    Default Re: time value format

    i tried this in conditional formatting, it doesn't work? time format issue?

    ??? it worked in my test file ...!
    Did you follow the steps I mentioned ??
    Can you give detailed example of your data.

  8. #8
    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
    ??? it worked in my test file ...!
    Did you follow the steps I mentioned ??
    Can you give detailed example of your data.[/COLOR]
    http://kel.ddns.net/f/a4c3596705/?raw=1

    i might have sth wrong i'm sorry
    even A1 is empty, B1 still colored.

    column B is lookup from database to return the last time of my employee clock out record
    plus, 1.5hr earlier than column B will color means they have overtime allowance.
    Last edited by kelvin_9; Oct 20th, 2019 at 09:17 AM.

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

    Default Re: time value format

    Some Comments
    File sample is welcome ...!
    If there is no value in A1 of course it cannot work, because we are comparing the time cell value to A1
    conditional formatting which is greater than A1
    You want to check time and not date ... !

    So what is the value in A1, where the time value as reference ???

  10. #10
    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
    Some Comments
    File sample is welcome ...!
    If there is no value in A1 of course it cannot work, because we are comparing the time cell value to A1


    You want to check time and not date ... !

    So what is the value in A1, where the time value as reference ???
    time enter by me, even i enter 21:00, it's still not working fine
    as i said, i have manual change both format cell as "hh:mm"

    p.s. the file entered time 21:00 and tested again

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
  •