Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting date range for counts | Excel Worksheet Functions | |||
selecting the first date in a range of dates | Excel Worksheet Functions | |||
USING THE NESTED IF FUNCTION TO SEE IF A TIME DATE VALUE IS T OR F | Excel Discussion (Misc queries) | |||
Selecting data within a date range | Excel Worksheet Functions | |||
Help! Selecting data according to date range | Excel Discussion (Misc queries) |