Between Times

  • Thread starter Thread starter AntonyY
  • Start date Start date
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
 
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.
 
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
 
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)
 
Back
Top