#1   Report Post  
Old January 9th 05, 11:59 PM
Niki
 
Posts: n/a
Default due dates

Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback at
all.
Niki

"Max" wrote:

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK

Click Add (this adds condition 3)

Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...

I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input


--
canman
------------------------------------------------------------------------
canman's Profile:

http://www.excelforum.com/member.php...o&userid=14596
View this thread: http://www.excelforum.com/showthread...hreadid=262235





  #2   Report Post  
Old January 10th 05, 02:14 AM
Kevin H. Stecyk
 
Posts: n/a
Default

Hi Niki,

I've tested this so you should be okay.

Assume your dates are in column A.

Select your dates in col A

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is |
=(AND(TODAY()=DATE(YEAR(A1),MONTH(A1),1),TODAY()< (DATE(YEAR(A1),MONTH(A1)+1,1)-1)))
Click Format button Patterns tab Yellow? OK
Click Add (this adds condition 2)

Condition 2
Formula Is | =TODAY()=DATE(YEAR(A1),MONTH(A1),1)-1
Click Format button Patterns tab Red? OK

Done.

Condition 1 checks to see if today's date is greater than the first day of
this the month referenced in Col A AND less than the last day of the month
referenced in Col A.
Condition 2 checks to see if today's date is greater than the last day of
the prior month referenced in Col A.

I hope this helps.

Best regards,
Kevin


"Niki" wrote in message
news
Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration
dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback
at
all.
Niki

"Max" wrote:

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK

Click Add (this adds condition 3)

Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...

I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input


--
canman
------------------------------------------------------------------------
canman's Profile:

http://www.excelforum.com/member.php...o&userid=14596
View this thread:
http://www.excelforum.com/showthread...hreadid=262235






  #3   Report Post  
Old January 10th 05, 02:25 AM
Max
 
Posts: n/a
Default

Hi, if you'd like to, send me a sample book at :

demechanik <at yahoo <dot com
or,
xdemechanik <atyahoo<dotcom

I'll see what can be done ..
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----
"Niki" wrote in message
news
Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration

dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback

at
all.
Niki

"Max" wrote:

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK

Click Add (this adds condition 3)

Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...

I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input


--
canman


------------------------------------------------------------------------
canman's Profile:

http://www.excelforum.com/member.php...o&userid=14596
View this thread:

http://www.excelforum.com/showthread...hreadid=262235






  #4   Report Post  
Old January 10th 05, 04:49 PM
ewize1
 
Posts: n/a
Default

If it completely ignored all the formattings, then what was shown?

I track due dates too, to resolution in term of days before and after a due
to track.

Presented here, is an improved version to what was previously discussed. It
eliminated the condition explicitly written to check whether cell is empty.

Assume due dates in column A.
Condition 1
Formula Is | =AND(B2<"",B2=TODAY())
= Today is the due date

Condition 2
Formula Is | =AND(B2<"",B2-TODAY()<=7)
= Date due in one week's time

Condition 3
Formula Is | =AND(B2<"",TODAY()B2)
= Overdue

If the above 3 conditions are not satisfied (i.e. date is due in more than a
week's time or cell is empty), the default format will be used.
The advantages of this method over the previous one a
1. It allows one more condition.
2. Blank cells are ignored.

The disadvantage of this method over the previous one is that it cannot
differentiate blank cells from cells with due dates longer than a week.

Regards.

"Niki" wrote:

Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback at
all.
Niki

"Max" wrote:

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK

Click Add (this adds condition 3)

Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...

I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input


--
canman
------------------------------------------------------------------------
canman's Profile:

http://www.excelforum.com/member.php...o&userid=14596
View this thread: http://www.excelforum.com/showthread...hreadid=262235




  #5   Report Post  
Old January 10th 05, 05:11 PM
ewize1
 
Posts: n/a
Default

Sorry, the dates are in column B instead of column A.

"ewize1" wrote:

If it completely ignored all the formattings, then what was shown?

I track due dates too, to resolution in term of days before and after a due
to track.

Presented here, is an improved version to what was previously discussed. It
eliminated the condition explicitly written to check whether cell is empty.

Assume due dates in column A.
Condition 1
Formula Is | =AND(B2<"",B2=TODAY())
= Today is the due date

Condition 2
Formula Is | =AND(B2<"",B2-TODAY()<=7)
= Date due in one week's time

Condition 3
Formula Is | =AND(B2<"",TODAY()B2)
= Overdue

If the above 3 conditions are not satisfied (i.e. date is due in more than a
week's time or cell is empty), the default format will be used.
The advantages of this method over the previous one a
1. It allows one more condition.
2. Blank cells are ignored.

The disadvantage of this method over the previous one is that it cannot
differentiate blank cells from cells with due dates longer than a week.

Regards.

"Niki" wrote:

Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback at
all.
Niki

"Max" wrote:

Perhaps try this ..

Assuming your due dates are listed in col B,
header in row1, dates in B2 down

Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc

Select col B

Click Format Conditional Formatting
Make these settings:

Condition 1
Formula Is | =B2=""
Leave it as "No format set"

Click Add (this adds condition 2)

Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK

Click Add (this adds condition 3)

Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK

Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...

I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input


--
canman
------------------------------------------------------------------------
canman's Profile:
http://www.excelforum.com/member.php...o&userid=14596
View this thread: http://www.excelforum.com/showthread...hreadid=262235






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
Ploting dates against a calendar and not as a simple events Barb Reinhardt Charts and Charting in Excel 2 January 22nd 05 03:41 AM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM
Entering dates in Excel 2002 DickG Excel Discussion (Misc queries) 3 January 9th 05 03:56 PM
I get wrong dates when i paste from a different sheet into a new s mmollat Excel Discussion (Misc queries) 2 January 6th 05 08:35 PM
Filtering with dates T Excel Discussion (Misc queries) 1 December 29th 04 08:01 PM


All times are GMT +1. The time now is 09:36 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017