Brain Scratcher: Compare Time Values

dyaron

Active Member
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jonmo1

MrExcel MVP
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..

mikerickson

MrExcel MVP
A1 is greater than B1, because A1 includes the date data as well as the time data.

dyaron

Active Member
is there a function that only captures the time data?

Jonmo1

MrExcel MVP
Yes, Mike is right...

so in A1 instead of just now(), put

=MOD(NOW(),1)

That will leave only the time in A1..

dyaron

Active Member
Awesome!!! You guys are great!

Jonmo1

MrExcel MVP
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.

Replies
5
Views
258
Replies
3
Views
785
Replies
2
Views
120
Replies
0
Views
685
Replies
0
Views
577

1,191,611
Messages
5,987,667
Members
440,104
Latest member
thigarette

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.

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

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