Microsoft Office Forums Formula to check if value falls between dates

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: 87
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 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: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
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
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,396
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
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: 87
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


All times are GMT -7. The time now is 03:58 AM.


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