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
david-(E-Mail Removed) 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
> >
> > david-(E-Mail Removed) 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
Similar Threads
