 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Greater than/Less than a certain Time
Hello, I've got a helper column (column B) with a formula that looks at a time stamp in column A. I'm trying to get each cell in column B to say Yes if the time in the adjoining cell in column A is earlier than 3:30 pm, and No if it's later than 3:30 pm. What I currently have is below, but this returns the error message #NAME? =IF(K2="","",IF(K2<3:30 PM,"yes","no")) I also tried putting the desired cutoff time in quotes (as below), but that returns 'no' no matter what time is in the cell. =IF(K2="","",IF(K2<"3:30 PM","yes","no")) What do I need to change to make this work? Thanks. Frank Try: =IF(K2="","",IF(K2<Timevalue("3:30 PM"),"yes","no")) Regards, Steve > Hello, > [quoted text clipped - 14 lines] > > Frank Hi Steve, Unfortunately, that still gives me 'no' even when the time is less that 3:30 PM. Frank >Try: > [quoted text clipped - 21 lines] >> >> Frank Formula is OK. Gives 'yes' for times less than 3:30 PM. Please check if your date cell (K2) is correctly formatted as 'time'. Joerg > Hi Steve, > [quoted text clipped - 28 lines] > >> > >> Frank > What do I need to change to make this work? A little bit of number type coercion: =IF(K2="","",IF(K2<(--"3:30 PM"),"yes","no")) The dbl-negative causes Excel to implicitly convert the text "3:30 PM" into a time value. Does that help? *********** Regards, Ron XL2002, WinXP > Hello, > [quoted text clipped - 14 lines] > > Frank Try this: =IF(K2="","",IF(K2<--"3:30 PM","yes","no")) > Hello, > [quoted text clipped - 14 lines] > > Frank Excellent! All works now. Formatting was wrong. Thank you very much everyone! Frank >Try this: > [quoted text clipped - 18 lines] >> >> Frank |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.