+ Reply to Thread
Results 1 to 10 of 10

Using Time function in If statement

  1. #1
    Registered User
    Join Date
    10-13-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    10

    Post Using Time function in If statement

    Hi,

    In A2 of my excel sheet, i have the formula as =NOW() which is displaying the time in the format 1.45.00 PM. The time gets updated to current time, every time the sheet refreshes. In cell B2 i have given a formula as =IF(A2>TIMEVALUE("01:45:00 PM"),"TRUE","FALSE"). What ever the time in A2, i always get value in B2 as TRUE.

    I have even tried the below formats. The intended logic of the function is not being derived though.

    =IF(A2="","",IF(A2>--"01:45:00 PM","TRUE","FALSE"))
    =IF(A2>TIME(01,45,00),"TRUE","FALSE")

    Is there any error in the formulas or is there any alternate method to get the correct value. I am unable to figure it out. Please assist me to resolve the issue.

    I am using Microsoft Excel 2013.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,092

    Re: Using Time function in If statement

    This,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    should probably be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-13-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    10

    Re: Using Time function in If statement

    Thanks TMS for your response.

    I have tried the time format as mentioned by you(=IF(A2>TIME(13,45,00),"TRUE","FALSE")). Still only TRUE gets displayed in B2 though the time in A2 is less than the time mentioned in the formula.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,389

    Re: Using Time function in If statement

    Check that the cells being looked at are formatted correctly as time.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    10-13-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    10

    Re: Using Time function in If statement

    Thanks AliGW for the response.

    I have selected the format of the cell A2 which contains the formula "NOW()" as Time. The format of the cell B2 which has the formula =IF(A2>TIME(13,45,00),"TRUE","FALSE") is also set as Time.

    I have even tried by setting the format of the two cells as custom(hh:mm:ss). Still, unable to get the result as per the logic in the formula.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Using Time function in If statement

    The formatting will only dictate how the value is displayed in the cell, not what the value actually is.
    =NOW() gets the date and time. Excel stores dates as days since Jan 1, 1900 and the date for today (August 28th, 2017) is 42975 . If you select cell A2 and look at the number formats (drop down on the 'Number' pane of the 'Home' tab) you'll see that 'Number', 'Currency' etc are displaying that number. On the other hand 13:45:00 is shown as 0.57. At 12:00 noon on Aug 28th the values being compared are 42975.5 and 0.57 (I hope that this is making sense).
    To compare times only use the formula: =IF(MOD(A2,1)>TIME(13,45,0),"TRUE","FALSE")
    Alternatively you could just use the following formula which will yield "Boolean Values" instead of text : =MOD(A2,1)>TIME(13,45,0)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    10-13-2015
    Location
    Hyderabad, India
    MS-Off Ver
    2007
    Posts
    10

    Re: Using Time function in If statement

    Thanks JeteMc for your resolution. Your formula is working exactly the way i wanted the logic to work.

    This resolves my issue. Thanks a lot to all of you for your swift replies.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Using Time function in If statement

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  9. #9
    Registered User
    Join Date
    08-29-2017
    Location
    pakistan
    MS-Off Ver
    2013
    Posts
    1

    Re: Using Time function in If statement

    A1 b1
    5:15 am tghu1314580
    5:05 am tghu0700640
    5:50 am tcku1432611

    how to get auto time updating in a1 when update value in b1.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Using Time function in If statement

    Hello Tanoli81 and Welcome to Excel Forum.
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    It will also be helpful if you can give examples of what you mean by 'time updating in A1' when the value of B1 is updated.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. IF statement involving time subtraction versus entered time data
    By JasonTran in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-08-2016, 11:43 AM
  2. [SOLVED] IF statement on data validation, End time greater than Start time
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2014, 08:04 AM
  3. Replies: 4
    Last Post: 03-14-2014, 07:08 AM
  4. [SOLVED] IF function: Early/On Time/Late Time vs. set window of time
    By hclark579 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 05:37 PM
  5. IF statement not working with text function and edate function.
    By joshnathan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-04-2013, 10:26 AM
  6. If statement to determine if time value is higher than another time value
    By xtort81 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-15-2013, 03:48 PM
  7. [SOLVED] Extracted time from date/time field does not flag on =IF(TIME function.
    By Vlad717 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2013, 03:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1