Formula to check if value falls between dates

shabbaranks · #1 04-29-2019, 03:23 AM

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

p45cal · #2 04-29-2019, 04:54 AM

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","")))))

Pecoflyer · #3 04-29-2019, 07:54 AM

@ 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)

shabbaranks · #4 04-30-2019, 02:37 AM

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

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