Microsoft Office Forums Formula to check if value falls between dates
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-29-2019, 03:23 AM
 shabbaranks Windows 7 64bit Office 2007 Advanced Beginner Join Date: Mar 2011 Posts: 87
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
#2
04-29-2019, 04:54 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 253

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","")))))`
#3
04-29-2019, 07:54 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,386

@ 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.
#4
04-30-2019, 02:37 AM
 shabbaranks Windows 7 64bit Office 2007 Advanced Beginner Join Date: Mar 2011 Posts: 87

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post OTPM Excel 7 02-03-2016 09:11 PM rindelsk Excel 1 04-06-2015 01:15 AM hionman Excel 5 11-12-2014 09:56 AM Sophie1 Excel 2 04-23-2014 07:19 AM gbaker Excel 6 10-07-2012 07:06 AM

All times are GMT -7. The time now is 10:07 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top