#1
|
|||
|
|||
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
|
||||
|
||||
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",""))))) 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",""))))) 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
|
||||
|
||||
@ 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 |
#4
|
|||
|
|||
Apologies I should have added a sample spreadsheet. On the plus side that formula is working so thank you.
|
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 |