Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-29-2019, 03:23 AM
shabbaranks shabbaranks is offline Formula to check if value falls between dates Windows 7 64bit Formula to check if value falls between dates Office 2007
Advanced Beginner
Formula to check if value falls between dates
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default Formula to check if value falls between dates

Hi,


I was wondering if anyone could help me build a formula which checks to see if one cell value falls between two other cell values and if it does then enter another value. Also I was wondering if its possible to have this check multiple dates for example:


If Cell A (date value) is greater than or equal to Cell B (date value) or Less than or equal to Cell C (Date Value) then enter Cell D, else greater than or equal to.... As per this


=IF(C2>=Doubles!B3<=Doubles!C3,"Week 1",IF(Qry_WTimsheetwithItems!C2>=Doubles!B8<=Doubl es!C8,"Week 2",IF(Qry_WTimsheetwithItems!C2>=Doubles!B13<=Doub les!C13,"Week 3",IF(Qry_WTimsheetwithItems!C2>=Doubles!B18<=Doub les!C18,"Week 4",IF(Qry_WTimsheetwithItems!C2>=Doubles!B23<=Doub les!C23,"Week 5","")))))


Thanks
Reply With Quote
  #2  
Old 04-29-2019, 04:54 AM
p45cal's Avatar
p45cal p45cal is offline Formula to check if value falls between dates Windows 10 Formula to check if value falls between dates Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

This is what I think you're looking for if I adjust exactly what you posted:
Code:
=IF(AND(C2>=Doubles!B3,C2<=Doubles!C3),"Week 1",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B8,Qry_WTimsheetwithItems!C2<=Doubles!C8),"Week 2",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B13,Qry_WTimsheetwithItems!C2<=Doubles!C13),"Week 3",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B18,Qry_WTimsheetwithItems!C2<=Doubles!C18),"Week 4",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B23,Qry_WTimsheetwithItems!C2<=Doubles!C23),"Week 5","")))))
but the first AND doesn't refer to the Qry_WTimsheetwithItems sheet, so I suspect that this formula might be on the Qry_WTimsheetwithItems sheet itself in which case most of those references can be scrapped leaving:
Code:
=IF(AND(C2>=Doubles!B3,C2<=Doubles!C3),"Week 1",IF(AND(C2>=Doubles!B8,C2<=Doubles!C8),"Week 2",IF(AND(C2>=Doubles!B13,C2<=Doubles!C13),"Week 3",IF(AND(C2>=Doubles!B18,C2<=Doubles!C18),"Week 4",IF(AND(C2>=Doubles!B23,C2<=Doubles!C23),"Week 5","")))))
but if I'm wrong then probably this:
Code:
=IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B3,Qry_WTimsheetwithItems!C2<=Doubles!C3),"Week 1",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B8,Qry_WTimsheetwithItems!C2<=Doubles!C8),"Week 2",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B13,Qry_WTimsheetwithItems!C2<=Doubles!C13),"Week 3",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B18,Qry_WTimsheetwithItems!C2<=Doubles!C18),"Week 4",IF(AND(Qry_WTimsheetwithItems!C2>=Doubles!B23,Qry_WTimsheetwithItems!C2<=Doubles!C23),"Week 5","")))))
Reply With Quote
  #3  
Old 04-29-2019, 07:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula to check if value falls between dates Windows 7 64bit Formula to check if value falls between dates 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

@ shabbaranks
After 80 + posts you should know that posting a sample sheet helps finding a solution and saves time.... (don't forget this is a FREE forum)
__________________
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
  #4  
Old 04-30-2019, 02:37 AM
shabbaranks shabbaranks is offline Formula to check if value falls between dates Windows 7 64bit Formula to check if value falls between dates Office 2007
Advanced Beginner
Formula to check if value falls between dates
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default

Apologies I should have added a sample spreadsheet. On the plus side that formula is working so thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check if a Date falls within a date range OTPM Excel 7 02-03-2016 09:11 PM
Formula to check if value falls between dates IF formula using dates rindelsk Excel 1 04-06-2015 01:15 AM
HELP: Return a value with a date that falls between two other dates hionman Excel 5 11-12-2014 09:56 AM
Formula to check if value falls between dates IF Formula to calculate dates Sophie1 Excel 2 04-23-2014 07:19 AM
If formula for subtracting dates gbaker Excel 6 10-07-2012 07:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:59 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