Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default If date in cell falls between date range...

I have column A with a date mm/dd/yyyy.
Column B is JAN, Column C is FEB, and so on.

If column A includes a date in JAN, I want the cell for Column C (FEB) to
highlight. In other words, the cell will need to be filled 30 days after the
date entered.

With the unique style of formatting for dates, I can't figure out how to
identify if cells include certain dates, etc. Any ideas?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default If date in cell falls between date range...



"Keep It Simple Stupid" wrote:

I have column A with a date mm/dd/yyyy.
Column B is JAN, Column C is FEB, and so on.

If column A includes a date in JAN, I want the cell for Column C (FEB) to
highlight. In other words, the cell will need to be filled 30 days after the
date entered.

With the unique style of formatting for dates, I can't figure out how to
identify if cells include certain dates, etc. Any ideas?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default If date in cell falls between date range...

Change your months to dates, starting in col a, as 31/12/06.
Fill across your selection by months (edit, fill, series, col, month)

Select cell b3. Conditional Formatting, select Formula instead of value.
Insert this formula"=AND($A3E$1+1,$A3<=F$1)" and select the colour you want
to use.

John

www.magicspreadsheets.com

"Keep It Simple Stupid" wrote:

I have column A with a date mm/dd/yyyy.
Column B is JAN, Column C is FEB, and so on.

If column A includes a date in JAN, I want the cell for Column C (FEB) to
highlight. In other words, the cell will need to be filled 30 days after the
date entered.

With the unique style of formatting for dates, I can't figure out how to
identify if cells include certain dates, etc. Any ideas?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default If date in cell falls between date range...

I don't think I explained my scenario enough.

In Row 1 of Columns B-M, the Column Headings are JAN-DEC, respectively.
Column A, Row 2, the user will enter a date something was done. I want the
"scheduled follow up months" to be automatically highlighted in columns B-M
of Row 2.

If Col A, Row 2 says 1/15/07, I want Col C,Row 2 to be highlighted because
it will correspond with FEB (30 days after the date entered in Col A, Row 2).

I am not sure if I understand your response completely, but it didn't seem
to work. Please let me know if I could have done something incorrectly.

"John Lyons" wrote:

Change your months to dates, starting in col a, as 31/12/06.
Fill across your selection by months (edit, fill, series, col, month)

Select cell b3. Conditional Formatting, select Formula instead of value.
Insert this formula"=AND($A3E$1+1,$A3<=F$1)" and select the colour you want
to use.

John

www.magicspreadsheets.com

"Keep It Simple Stupid" wrote:

I have column A with a date mm/dd/yyyy.
Column B is JAN, Column C is FEB, and so on.

If column A includes a date in JAN, I want the cell for Column C (FEB) to
highlight. In other words, the cell will need to be filled 30 days after the
date entered.

With the unique style of formatting for dates, I can't figure out how to
identify if cells include certain dates, etc. Any ideas?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default If date in cell falls between date range...

In row 1, change your months to dates, starting in col a, as 31/12/06.
Fill across your selection by months (edit, fill, series, col, month)
Format row 1 "mmm". This will give you JAN, FEB, etc.

Select cell b3. Conditional Formatting, select Formula instead of value.
Insert this formula"=AND(($A3+30)E$1+1,($A3+30)<=F$1)" and select the
colour you want to use.

It works on my laptop!


"Keep It Simple Stupid" wrote:

I don't think I explained my scenario enough.

In Row 1 of Columns B-M, the Column Headings are JAN-DEC, respectively.
Column A, Row 2, the user will enter a date something was done. I want the
"scheduled follow up months" to be automatically highlighted in columns B-M
of Row 2.

If Col A, Row 2 says 1/15/07, I want Col C,Row 2 to be highlighted because
it will correspond with FEB (30 days after the date entered in Col A, Row 2).

I am not sure if I understand your response completely, but it didn't seem
to work. Please let me know if I could have done something incorrectly.

"John Lyons" wrote:

Change your months to dates, starting in col a, as 31/12/06.
Fill across your selection by months (edit, fill, series, col, month)

Select cell b3. Conditional Formatting, select Formula instead of value.
Insert this formula"=AND($A3E$1+1,$A3<=F$1)" and select the colour you want
to use.

John

www.magicspreadsheets.com

"Keep It Simple Stupid" wrote:

I have column A with a date mm/dd/yyyy.
Column B is JAN, Column C is FEB, and so on.

If column A includes a date in JAN, I want the cell for Column C (FEB) to
highlight. In other words, the cell will need to be filled 30 days after the
date entered.

With the unique style of formatting for dates, I can't figure out how to
identify if cells include certain dates, etc. Any ideas?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need true or false if a date falls between a date range dustin Excel Worksheet Functions 3 December 9th 06 03:01 AM
update cell only if now() falls in a given date range treyn1292 Excel Worksheet Functions 0 February 16th 06 09:54 PM
Return a specified date when it falls within a range.... Nokose451 Excel Discussion (Misc queries) 1 January 16th 06 11:06 PM
Can I check if a date falls between a range then sum other # if tr clovins1 Excel Worksheet Functions 2 October 9th 05 02:14 AM
how to count if the value falls between a date range joe Excel Discussion (Misc queries) 3 September 28th 05 04:37 AM


All times are GMT +1. The time now is 01:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"