determine if value is between two dates

[flashgordie]

-------a---------|------ b--------|-----c-----|-----d----|
1----------------|----------------| $500.00---| $299.00--|
2----------------|----------------|----22-----|-----7----|
3 Fri Dec-09-11 | Thu Dec-22-11 |
4 Fri Dec-23-11 | Thu Jan-05-12 |
5 Fri Jan-06-1 | Thu Jan-19-12 |


Hello, can anyone help with my problem. I am trying find a formula that will determine in C2 is between the dates of a3 and b3. IF it is, I would like it to return a value of c1.

second example find if d2 is between a3 and b3 and if so, return d1

Thank you for your help

 

[Scott Huish]

In your example, it's not really determinable as your "dates" in C2 and D2 do not include a month and a year.

 

[flashgordie]

Thanks HotPepper, I understand where you are coming from, in my example the value of c2 represents the 22nd of each month, similarly d2 is the 7th of each month. I was hoping that by formatting the cell as a date (more specifically a day) that this type of determination might be able to be made.

I was hoping not to have to state a date for each month, dec 22, jan 22, feb 22. This would seem a little much.

 

[flashgordie]

Hi HotPepper, thanks for pointing out that problem with the single date. I went back a looked at it again and eve nthough I formatted the values in Row 2 as dates using a custom "dd", excel actually posts the value as "dd-Jan-1900". Any ideas?