# Using Time function in If statement

1. ## 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.  Register To Reply

2. ## Re: Using Time function in If statement

This,
Formula:  `Please Login or Register  to view this content.`
should probably be
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Using Time function in If statement

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.  Register To Reply

4. ## Re: Using Time function in If statement

Check that the cells being looked at are formatted correctly as time.  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

9. ## 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.  Register To Reply

10. ## 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.

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.  Register To Reply

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