date / time format - and nested formulas selecting from a range
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... |
date / time format - and nested formulas selecting from a range
On Wed, 19 Aug 2009 19:54:01 -0700, NinasNumber
wrote: 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 |
date / time format - and nested formulas selecting from a rang
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. "Ron Rosenfeld" wrote: On Wed, 19 Aug 2009 19:54:01 -0700, NinasNumber wrote: 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 |
date / time format - and nested formulas selecting from a rang
"NinasNumber" wrote:
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:m m"), 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 ----- "NinasNumber" wrote in message ... 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. "Ron Rosenfeld" wrote: On Wed, 19 Aug 2009 19:54:01 -0700, NinasNumber wrote: 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 |
All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com