if function - logical test = date range ?

[tludicke]

I would like a certain value to appear if the date in a particular column falls within a time period.

What would a formula for that look like?

- Ted

 

[iliace]

Along these lines:

Excel Workbook
	A	B	C
1	From	1/1/2010
2	To	1/31/2010
3	Date 1	1/15/2010	certain value
4	Date 2	2/15/2010	uncertain value
sheet

 

[tludicke]

I would like a certain value to appear if the date in a particular column falls within a time period.

What would a formula for that look like?

- Ted

Let me rephrase that since not too many lookers:<link id="Main-File" rel="Main-File" href="file://localhost/Users/fcadmin/Library/Caches/TemporaryItems/msoclip1/01/clip.htm"> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:left; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {color:black; font-family:Arial, sans-serif; mso-font-charset:0; vertical-align:bottom;} .xl25 {color:black; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:"m\\\/d\\\/yyyy\;\@"; vertical-align:bottom;} .xl26 {vertical-align:bottom;} .xl27 {mso-number-format:"Short Date";} .xl28 {color:black; font-size:12.0pt; font-family:Arial, sans-serif; mso-font-charset:0; vertical-align:bottom; white-space:normal;} .xl29 {color:black; font-size:12.0pt; font-family:Arial, sans-serif; mso-font-charset:0; vertical-align:bottom; white-space:normal;} .xl30 {font-size:12.0pt; font-family:Arial, sans-serif; mso-font-charset:0; vertical-align:bottom; white-space:normal;} .xl31 {font-size:12.0pt;} --> </style> <table style="border-collapse: collapse; width: 457px; height: 105px;" border="0" cellpadding="0" cellspacing="0"> <!--StartFragment--> <col style="" span="2" width="60"> <col style="" width="60"> <tbody><tr height="12"> <td class="xl28" height="12" width="60">Start Date</td> <td class="xl28" width="60">End Date</td><td class="xl31" width="60">FLAG(value)</td></tr><tr height="12"> <td class="xl27" x:num="40233.0" align="left" height="12">2/24/10</td><td class="xl27" x:num="40330.0" align="left">6/1/10</td><td></td> </tr> <tr height="12"> <td class="xl25" x:num="39678.0" align="left" height="12">8/18/2008</td> <td class="xl25" x:num="39988.0" align="left">6/24/2009</td> <td></td> </tr> <tr height="12"> <td class="xl25" x:num="40168.0" align="left" height="12">12/21/2009</td> <td class="xl25" x:num="40345.0" align="left">6/16/2010</td> <td></td> </tr> <!--EndFragment--> </tbody></table>

 

[iliace]

Where is the column with the date that may or may not fall within the ranges?

 

[tludicke]

Where is the column with the date that may or may not fall within the ranges?

Thanks - I didn't see your original response. The End Date (contract expiration date in this instance) is what I need to flag. When I pull up the worksheet (usually on a weekly basis), rather than scrolling down through 300 lines looking to see if a date is nearing [+7 days] or already passed, it would be very helpful if there were a Flag, such as "WATCH" and "EXPIRED". This worksheet is shared by others who input new contracts daily.

Thanks!

 

[iliace]

Instead of flagging, I would suggest you use conditional formatting. In this example, any date that's prior to today's date is highlighted in red; anything ending within 30 days is yellow. It's a much clearer visual cue. Today's date is in D2, for reference.

<b>sheet</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:71px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial; font-size:12pt; text-align:left; ">Start Date</td><td style="font-family:Arial; font-size:12pt; text-align:left; ">End Date</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; font-size:10pt; text-align:left; ">2/24/2010</td><td style="font-family:Arial; font-size:10pt; text-align:left; ">6/1/2010</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">3/3/2010</td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; font-size:10pt; text-align:left; ">8/18/2008</td><td style="background-color:#ff0000; font-family:Arial; font-size:10pt; text-align:left; ">6/24/2009</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td></tr><tr style="height:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; font-size:10pt; text-align:left; ">12/21/2009</td><td style="background-color:#ffff00; font-family:Arial; font-size:10pt; text-align:left; ">3/15/2010</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=TODAY()</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B2</td><td >1. / Formula is =B2<TODAY()</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >B2</td><td >2. / Formula is =B2<(TODAY()+30)</td><td style="background-color:#ffff00; ">Abc</td></tr></table></td></tr></table>

You can use the same principle to flag in a separate column, if you want.

 

[LHaase0217]

I am wanting something similar however a bit different and conditional formatting just isn't the answer in my case. I want an IF function to enter the value from cell K21, for instance, which is a dollar value, to be entered in cell AA21 if the date in B21 is between 11/16/2004 and 7/4/2007. I intend to total this AA column up at the end of the data entry. Any ideas?

 

[AhoyNC]

It is usually better to start a new post instead of adding to an old one.

Try:

Excel Workbook
	A	B	C	K	L	AA	AB
20
21		5/6/2006		$10.00		$10.00
22
Sheet

 

[LHaase0217]

It is usually better to start a new post instead of adding to an old one.

Try:

*	A	B	C	K	L	AA	AB
*	*	*	*	*	*	*
*	*	*	*
*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:68px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]5/6/2006[/TD]

[TD="align: right"]$10.00[/TD]

[TD="align: right"]$10.00[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula AA21 =IF(AND(B21>=DATE(2004,11,16),B21<=DATE(2007,7,4)),K21,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

PERFECT!! Thank you so much. I see now where I was going wrong.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum. If your between dates change from time to time you may want to put them in separate cells and refer to those cells in the formula.