Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Senior Member
 
Posts: 105
Default Date Conditional Formatting

Hello,

I need a formula to help in doing conditional formatting for date

say for Example- Date given is 25/02/2012
When this date has only one month left to cross one year, that is,if today's date is 25/01/2013, it should turn yellow and when this date crosses one year,that is, if today's date is 25/02/2013, it should turn red.

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Date Conditional Formatting

Excel Dumbo expressed precisely :
Hello,

I need a formula to help in doing conditional formatting for date

say for Example- Date given is 25/02/2012
When this date has only one month left to cross one year, that is,if
today's date is 25/01/2013, it should turn yellow and when this date
crosses one year,that is, if today's date is 25/02/2013, it should turn
red.

Thanks


That will require setting 2 conditions. I don't see any point in
setting a condition if the date is less than 1 year, and so I suggest
setting CF to handle only if the date crosses one year.

If you need a visual indicator )other than the default fill color) that
the date hasn't crossed yet then you can manually set fill color for
the range.

If you want to *flag* a date that's about to cross a year (last day)
then set its CF condition 1st, set the other one last...


For flagging last day before crossing (yellow)
=($A1+365)=TODAY()

For crossing the year (red)
=(TODAY()-366)=$A1

...where the number of days (365/366) account for 2012 being a leap.
Adjust this to your liking.<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


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
conditional formatting for cell date to equal today's date Sistereinstein Excel Worksheet Functions 2 September 10th 12 07:53 PM
conditional formatting a date Neilvass Excel Discussion (Misc queries) 1 December 18th 08 01:49 PM
Conditional Formatting by date lola5375 Excel Discussion (Misc queries) 6 October 31st 08 11:01 PM
date conditional formatting? Frank[_2_] New Users to Excel 3 April 12th 07 05:17 PM
conditional DATE formatting? LTUser54 Excel Discussion (Misc queries) 2 May 18th 06 09:24 PM


All times are GMT +1. The time now is 06:00 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"