I need to wirte an if statement that looks at a time stamp with date/time and
compatare it to the employees start time. If the employee comes in past their
start time, tardy, if before OK. Employees have all sorts of start times and
cross midnight.
Thanks,
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.

Your criteria (first argument) in your IF statement could be a simple
subtraction of the times if you make your stamp date and time.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312

I can get it to subtract the two numbers and come up with a numeric or time
formatted answer. I seem to be missing some important detail to make the IF
statement work right.

For example.
Log in time Reg. Start Time Late or not
5/1/06 7:02 7:00 ???
What formula could I use to get the answer? I can get the 2 minute
difference, or the 23:58 minute difference, but I cannot get a True or False
answer in my IF statement to work. I can get half of it, reverse the equation
and get the other half, but I cannot get it to work in both cases.

--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"mrice" wrote:


Your criteria (first argument) in your IF statement could be a simple
subtraction of the times if you make your stamp date and time.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312

You could test the value of your subtraction for being greater than or
less than zero and get your tru or false that way.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312

Try this equation:

=IF(B7-A70, "late", "ok")

eg:

Due time Start time result
7:00:00 6:58:00 ok
7:00:00 7:00:00 ok
7:00:00 7:00:01 late

Have your employee name (or ID) and dates as separate columns (better for
sorting etc.)

Format your A & B Columns as time (hh:mm:ss), you can subtract either one
from the other, but you have to ensure that you don't result in a negative
time, as Excel won't like it. (Lets be honest you can't actually have
negative time)

"mrice" wrote:


You could test the value of your subtraction for being greater than or
less than zero and get your tru or false that way.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312

Thanks for your help so far.
This is what I have as of now.
I had to create another column and formatted as hh:mm Then insert the
formula MOD(B2,1). This gave me the time value of the starttime (column B2)
which was Date/Time and pulled from the timeclock application.
Then for the If statement it looks like so.
IF((F2=G2),"OK",IF((F2<G2),"Early",IF((F2G2),"Tar dy",)))
F=Mod(starttime,1)
G=Employee daily start time.

Now the only issue sleft to resolve is the seconds. I need to round them
down in column F. MOD(B2,1) is the current formula in column F.
Any ideas?
Or would it be better to add 1 minute to the employee start time?

--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"Simon G" wrote:

Try this equation:

=IF(B7-A70, "late", "ok")

eg:

Due time Start time result
7:00:00 6:58:00 ok
7:00:00 7:00:00 ok
7:00:00 7:00:01 late

Have your employee name (or ID) and dates as separate columns (better for
sorting etc.)

Format your A & B Columns as time (hh:mm:ss), you can subtract either one
from the other, but you have to ensure that you don't result in a negative
time, as Excel won't like it. (Lets be honest you can't actually have
negative time)

"mrice" wrote:


You could test the value of your subtraction for being greater than or
less than zero and get your tru or false that way.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312

Maybe you could do something with the TimeSerial Function populating it
with just the hours and minutes.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=540312