How can I calculate if a user is in time? (How does NOW() work?)
I know there's a lot of tutorials and stuff about this kind of things, but I have searched and based in what I watched here and here (sorry if both are in spanish).
I want to now, using the hour of now()
(in cell B3
) if B3
is in the range of 8:00:00 (A2
) and 13:00:00 (B2
)
A | B | C | |
---|---|---|---|
1 | ENTRY | EXIT | In time? |
2 | 8:00:00 | 13:00:00 | FORMULA |
3 | Actual time: | 8:40 |
Using what I saw on the internet, I'm using this formula in c2
:
=if(and(value(B3)=>(value(A2)),(value(B3))<=(value(B2))),"In time","Not in time")
As you can see, my time is 8:40:00, so it is between 8:00:00 and 13:00:00, I would like some help to know what am I doing wrong (maybe it is just a fool issue but I'm not very good at excel) because, depending on the blogs I watched, it should work and show "In time" in c2
and it is showing "Not in time".
And I would like to do it with now()
, because, when I hardcoded the hour by simply just typing "8:40:00", it works.
EDIT: All cells are in format "Hour".
The now() function will include a date by default. In cell B3, change your formula to this:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
This will extract only the time from the current now datetime.
I solve it, here it says I have to convert it to the nearest int, so in b3
I changed this:
= now()
to this:
= now()-int(now())
Added more details:
I changed because the now()
function changes only the display format, but the value in the cell is still a decimal number.
By adding int()
, this takes the decimal value of now()
and converts it into the nearest integer.