Compare times : IF time < time2 then
 

Hi,

I'm stuck on a comarison of times problem.

My time column is formatted as "dd/mm/yyyy hh:mm:ss" although the
cell is formatted as hh:mm:ss

I'd like a comparison to say if the time is less than say 14:00 then
TRUE else FALSE but a simple IF (A2<$C$1,"TRUE","FALSE") doesn't work.

cell C1 is 14:00:00 formatted as hh:mm:ss

A simple time subtraction works OK ie A2-C1

If I could get rid of the DATE part of the field then the comparison
works OK. Trying a RIGHT function to just put the time part in a column
by itself doesn't work.

Any ideas, as I'm stuck.

regards

Davy

Compare times : IF time < time2 then
 

Try this:

=IF(MOD(A2,1)<$C$1,"TRUE","FALSE")

assuming your date/time is in A2. If it is in the other cell, try this:

=IF(A2<MOD($C$1,1),"TRUE","FALSE")

Hope this helps.

Pete

wrote:
Hi,

I'm stuck on a comarison of times problem.

My time column is formatted as "dd/mm/yyyy hh:mm:ss" although the
cell is formatted as hh:mm:ss

I'd like a comparison to say if the time is less than say 14:00 then
TRUE else FALSE but a simple IF (A2<$C$1,"TRUE","FALSE") doesn't work.

cell C1 is 14:00:00 formatted as hh:mm:ss

A simple time subtraction works OK ie A2-C1

If I could get rid of the DATE part of the field then the comparison
works OK. Trying a RIGHT function to just put the time part in a column
by itself doesn't work.

Any ideas, as I'm stuck.

regards

Davy

Compare times : IF time < time2 then
 

Genius, thanks

this had me completely stuck. If you've got the time could you tell
me why it works?


Cell A2=30/11/1999 15:01:00

MOD(A2,1)=15:01:00 (formatted as time)

Regards

Davy
Pete_UK wrote:
Try this:

=IF(MOD(A2,1)<$C$1,"TRUE","FALSE")

assuming your date/time is in A2. If it is in the other cell, try this:

=IF(A2<MOD($C$1,1),"TRUE","FALSE")

Hope this helps.

Pete

wrote:
Hi,

I'm stuck on a comarison of times problem.

My time column is formatted as "dd/mm/yyyy hh:mm:ss" although the
cell is formatted as hh:mm:ss

I'd like a comparison to say if the time is less than say 14:00 then
TRUE else FALSE but a simple IF (A2<$C$1,"TRUE","FALSE") doesn't work.

cell C1 is 14:00:00 formatted as hh:mm:ss

A simple time subtraction works OK ie A2-C1

If I could get rid of the DATE part of the field then the comparison
works OK. Trying a RIGHT function to just put the time part in a column
by itself doesn't work.

Any ideas, as I'm stuck.

regards

Davy

Compare times : IF time < time2 then
 

Genius, thanks

this had me completely stuck. If you've got the time could you tell
me why it works?


Cell A2=30/11/1999 15:01:00

MOD(A2,1)=15:01:00 (formatted as time)

Regards

Davy
Pete_UK wrote:
Try this:

=IF(MOD(A2,1)<$C$1,"TRUE","FALSE")

assuming your date/time is in A2. If it is in the other cell, try this:

=IF(A2<MOD($C$1,1),"TRUE","FALSE")

Hope this helps.

Pete

wrote:
Hi,

I'm stuck on a comarison of times problem.

My time column is formatted as "dd/mm/yyyy hh:mm:ss" although the
cell is formatted as hh:mm:ss

I'd like a comparison to say if the time is less than say 14:00 then
TRUE else FALSE but a simple IF (A2<$C$1,"TRUE","FALSE") doesn't work.

cell C1 is 14:00:00 formatted as hh:mm:ss

A simple time subtraction works OK ie A2-C1

If I could get rid of the DATE part of the field then the comparison
works OK. Trying a RIGHT function to just put the time part in a column
by itself doesn't work.

Any ideas, as I'm stuck.

regards

Davy

Compare times : IF time < time2 then
 

Davy,

dates are stored internally by Excel as integers - the number of
elapsed days from some reference date (1st Jan 1900). Times are stored
internally as fractions of a 24-hour day, so that 6:00:00 is actually
stored as 0.25 (quarter of a day). So, dates and times can be added
together and the whole number part represents the date and the
fractional part is the time.

The MOD( ) function gives the remainder after division, so MOD(A1,1)
will give just the time part of a date/time value in A1. Alternatively,
INT(A1) would give just the date part.

Hope this helps, and I'm glad you got it to work.

Pete

wrote:
Genius, thanks

this had me completely stuck. If you've got the time could you tell
me why it works?


Cell A2=30/11/1999 15:01:00

MOD(A2,1)=15:01:00 (formatted as time)

Regards

Davy
Pete_UK wrote:
Try this:

=IF(MOD(A2,1)<$C$1,"TRUE","FALSE")

assuming your date/time is in A2. If it is in the other cell, try this:

=IF(A2<MOD($C$1,1),"TRUE","FALSE")

Hope this helps.

Pete

wrote:
Hi,

I'm stuck on a comarison of times problem.

My time column is formatted as "dd/mm/yyyy hh:mm:ss" although the
cell is formatted as hh:mm:ss

I'd like a comparison to say if the time is less than say 14:00 then
TRUE else FALSE but a simple IF (A2<$C$1,"TRUE","FALSE") doesn't work.

cell C1 is 14:00:00 formatted as hh:mm:ss

A simple time subtraction works OK ie A2-C1

If I could get rid of the DATE part of the field then the comparison
works OK. Trying a RIGHT function to just put the time part in a column
by itself doesn't work.

Any ideas, as I'm stuck.

regards

Davy