NinasNumber said:

Thanx for your quick response. I actually tried that but it didn't work.

the name cells are strickly an hour min cell, I think the mod function

still retains the date.

No, it doesn't. Date and time values are stored internally as an integer

representing the date and a decimal fraction representing the time (i.e. the

fractional part of a day).

MOD(A1,1) extracts the decimal fraction; so it does indeed remove the date

value. (More accurately, it replaces the date value with zero, which is the

date value of Jan 0, 1900, which you might see if you format the time value

with "m/dd/yyyy hh:mm".)

I wonder if your formula does not work because of other considerations

related to the internal representation of date/time values (viz. binary

floating point).

Try the following formula instead:

=IF(--text(mod(E18,1),"hh:mm")>=npk,

IF(--text(mod(E18,1),"hh:mm")>=peakaft,

IF(--text(mod(E18,1),"hh:mm")>=nonpeakeve,

IF(--text(mod(E18,1),"hh:mm")>=$F$12, "No Shuttle",

"NonPeak"),"Peak"),"Non Peak"),"Peak")

This assumes that "npk", "peakaft" etc refer to cells that contain

constants. If, on the other hand, those cells contain arithmetic

expressions, the following might work better for you:

=IF(text(mod(E18,1),"hh:mm")>=text(npk,"hh:mm"),

IF(text(mod(E18,1),"hh:mm")>=text(peakaft,"hh:mm"),

IF(text(mod(E18,1),"hh:mm")>=text(nonpeakeve,"hh:mm"),

IF(text(mod(E18,1),"hh:mm")>=text($F$12,"hh:mm"), "No Shuttle",

"NonPeak"),"Peak"),"Non Peak"),"Peak")

By the way, this could be written more compactly and more efficiently if you

used a look-up table.

The purpose of the TEXT() expression is to truncate (round down) to the

precision of a minute. This might be necessary if the GPS data is precise

to the second. And eliminates infinitesimal numerical aberrations that can

arise as a result of any floating point operation, such as "identifying the

time between departures [by] a simple subtraction formula".

----- original message -----