date / time format - and nested formulas selecting from a range

[NinasNumber]

I am trying to identify compliance with a shuttle departure based on peak
time criteria, or non peak time criteria. My worksheet data is copied from
an online gps report, which has the cell for departures and arrivals as a
date time format.
Identifying the time between departures is a simple subtraction formula.
When all relevant columns are simply time with no date, this formula works:
I have 2 peak times and non peak times identified in the top of the sheet,
and an end time for the shuttle.

=IF(E18>=npk, IF(E18>=peakaft, IF(E18>=nonpeakeve, IF(E18>=$F$12,"No
Shuttle","NonPeak"),"Peak"),"Non Peak"),"Peak")

I either need to convert the column with the date time data to just time (it
doesn't work with formatting the cell, I would need to trim the cells--- ) --
I tried Mod(a1,1) which strictly shows the time, but the date data is still
buried, making the above formula useless...

 

[Ron Rosenfeld]

I am trying to identify compliance with a shuttle departure based on peak
time criteria, or non peak time criteria. My worksheet data is copied from
an online gps report, which has the cell for departures and arrivals as a
date time format.
Identifying the time between departures is a simple subtraction formula.
When all relevant columns are simply time with no date, this formula works:
I have 2 peak times and non peak times identified in the top of the sheet,
and an end time for the shuttle.

=IF(E18>=npk, IF(E18>=peakaft, IF(E18>=nonpeakeve, IF(E18>=$F$12,"No
Shuttle","NonPeak"),"Peak"),"Non Peak"),"Peak")

I either need to convert the column with the date time data to just time (it
doesn't work with formatting the cell, I would need to trim the cells--- ) --
I tried Mod(a1,1) which strictly shows the time, but the date data is still
buried, making the above formula useless...

If the values in E18 are the date-time that you are trying to make just a time,
and the other entries represent merely "time", then try:

=IF(mod(E18,1)>=npk, IF(mod(E18,1)>=peakaft, IF(mod(E18,1)>=nonpeakeve,
IF(mod(E18,1)>=$F$12,"No Shuttle","NonPeak"),"Peak"),"Non Peak"),"Peak")
--ron

 

[NinasNumber]

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.

 

[JoeU2004]

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 -----