Brain Scratcher: Compare Time Values

dyaron

Active Member
Joined
Nov 26, 2007
Messages
291
I want to do this in a cell, not vba.

I have a cell A1, that contains NOW(). It is formatted to show H:MM.

I have another cell B1, that contains a static time in which I test against. It is formatted to show H:MM as well, but the value is actually 17:00

My question is, I'm trying to create in another cell a formula that calculates data based on waht time it is. It's as such:

=if(A1<'B1Sum(A1:A30), Sum(A1:A29)... this doesn't work, because I merely tested to see if the comparison was working by doing the following:

=if(A1<'B1, "A1 is less than B1", "A1 is greater than B1")... and I keep returning the false statement "A1 is greater than B1"), even though this is not the case. Any thoughts?

***ignore that quote after the greater than signs, because the forum wouldn't let me post unless it was there?***
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
use code tags..

"["Code"]"
formula here
"["/Code"]"

Without the quotes

And if you have < or > in your formula, put spaces around them. The board software thinks it's HTML Tags...

=IF(A1 < B1,1,0) <---see the spaces..
 
Upvote 0
Yes, Mike is right...

so in A1 instead of just now(), put

=MOD(NOW(),1)

That will leave only the time in A1..
 
Upvote 0
No problem.

The IMPORTANT underlying lesson here is...

Formatting a cell only changes the APPEARANCE of the cell's value, the ACTUAL value of the cell remains the same. Formulas ignore formatting of cells and use only the actual values..

In MHO this is one of the most important/usefull/commonly forgotten rules in excel.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top