Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-29-2019, 03:23 AM
shabbaranks shabbaranks is offline Windows 7 64bit Office 2007
Advanced Beginner
 
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 Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
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 Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
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 Windows 7 64bit Office 2007
Advanced Beginner
 
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
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
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 10:07 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft