Between Times

A

AntonyY

How can I find out if I'm on time!! I've got a time stamp which puts
time in A1. In B1 is the time of 09:00 and in B2 is the time of 10:00
What is for formula to work out if the time is between 09:00 and 10:0
I'm on time and if it's before or after 09-10:00 I'm early or late?

Regards
Anton
 
O

Opinicus

One way:
=IF(A1<B1,"Early",IF(A1>B2,"Late","On-Time"))
Click to expand...

Why can't I get this to work? If I manually enter a time in
A1 the formula will work like it's supposed to. But if I
enter =NOW() in A1 the formula always returns "Late" no
matter what the values in A1, B1, and B2 may be.
 
S

Sandy Mann

Bob,

If you reformat the cells to show what XL is *really* storing in tghe cells
rather than what it is showing, you will see the reason why. For instance
format A1 & B1 as custom format "dd/mm/yyy hh:mm:ss" (without the quotes and
with your own regional date layout). Now when you enter the =NOW() formula
in A1 it will show something like:
21/11/2004 13:16:18

and entering the current time by "Control + Shift + :" it will show
something like:

00/01/1900 13:16:00

NOW() enters the date as today's date, (obviously), but Control + Shift + :
enters it as the time since the start of XL's universe 00/00/1900 00:00:00

A1 will therefore always be later than B1.

As a work around I tried entering an another cell =INT(NOW())+B1 which at
least put both cells in the same day and then reference that cell in the
formula.

HTH

Sandy
 
J

JE McGimpsey

NOW() inserts both the date and the time. XL stores dates as integral
offsets to a base date, and times as fractional days. So 21 November
2004 is stored as 38312 (1900 date system), or 36850 (1904 date
system), and 6:00 is stored as 0.25.

NOW() however (at 21 November 2004 06:00) returns 38312.25 or 26850.25.
This is larger than 10:00 (or 0.416666667), so the function returns
"Late"


To return *just* the time, you can use =MOD(NOW(),1)
 
Top