Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2014, 04:40 PM
tomseeley tomseeley is offline Help me with simple IF stmt to test date within a range Windows 7 64bit Help me with simple IF stmt to test date within a range Office 2010 64bit
Novice
Help me with simple IF stmt to test date within a range
 
Join Date: Jan 2014
Posts: 10
tomseeley is on a distinguished road
Default Help me with simple IF stmt to test date within a range

I'm embarrassed! I should know how to do this but I fergit!



I have a simple Excel database containing a column of dates. I want to test a date in any cell in that column and return "yes" in the adjacent cell if the date I'm testing is within a range of dates I'll input manually into the IF statement, and "no" if the date I'm testing is not within the range of dates I'll input manually into the IF statement.

Let's say cell A3 contains the date 2/21/2014. I want cell A4 to return "yes" if the date in A3 is >= 10/1/2013 and <= 12/31/2014, and I want cell A4 to return "no" if the date in A3 is either before 10/1/2013 or after 12/31/2014.

Since the AND argument does evaluate to "true" in this case, at least to my logical eye, I should get "yes" in cell A4, not "no".

When I type cell A4 =IF(AND(A3>=10/1/2013,<=12/31/2014),"yes","no") I get "no" in cell A4 regardless of what date values I type in manually in the IF and AND arguments.

If I simply enter dates 10/1/2013 and 12/31/2014 as dates in their own cells, and use those cell references in the argument for AND, it works. But I'd prefer not to do it that way. Is there a way to make Excel treat the character string 10/1/2013, for example, as a date in the AND argument, and not as text or numbers, which it appears to be doing?

Or must I simply enter the desired dates as dates in their own cells, etc.?
Reply With Quote
  #2  
Old 02-22-2014, 08:31 PM
macropod's Avatar
macropod macropod is offline Help me with simple IF stmt to test date within a range Windows 7 32bit Help me with simple IF stmt to test date within a range Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use:
=IF(OR(A3<DATE(2013,10,1),A3>DATE(2014,12,31)),"NO ","YES")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-23-2014, 11:20 AM
tomseeley tomseeley is offline Help me with simple IF stmt to test date within a range Windows 7 64bit Help me with simple IF stmt to test date within a range Office 2010 64bit
Novice
Help me with simple IF stmt to test date within a range
 
Join Date: Jan 2014
Posts: 10
tomseeley is on a distinguished road
Default Thanks!

Thank you! I didn't think of the OR statement. And I didn't know how to enter the year,month,day separated by commas either, so that Excel would interpret the info as a date! This does it for me!
Reply With Quote
  #4  
Old 02-23-2014, 12:42 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help me with simple IF stmt to test date within a range Windows 7 64bit Help me with simple IF stmt to test date within a range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Actually statements like A3>="10/1/2013" also work
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 02-23-2014, 02:13 PM
macropod's Avatar
macropod macropod is offline Help me with simple IF stmt to test date within a range Windows 7 32bit Help me with simple IF stmt to test date within a range Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Pecoflyer View Post
Actually statements like A3>="10/1/2013" also work
But, in this case, only on a PC whose regional settings use the MM/DD/YYYY format! Move to a PC using DD/MM/YYYY settings and you'll get 'interesting' results. The DATE function is impervious to such subtleties...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
test Cheekychino PowerPoint 1 04-08-2013 09:09 PM
Help me with simple IF stmt to test date within a range Write Test dkub Word VBA 1 07-21-2012 09:01 PM
Help me with simple IF stmt to test date within a range Date Range Prompt MikeWooZ Excel 6 12-04-2011 02:10 PM
Help me with simple IF stmt to test date within a range Vba, Is there a better way to test empty cells? Hwyn Excel Programming 2 11-14-2011 11:44 AM
Test after presentation? barnkat PowerPoint 0 08-13-2010 10:58 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:13 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft