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

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

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

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

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