Forum Discussion

Copper Contributor

Oct 31, 2017

Formatting a cell for date range

Hello all I need to format a cell so that when a date is entered that is within 12 months of today's date the cell is highlighted in green and when the date exceeds 12 months from today's date th...

Excel Desktop

Formulas & Functions

Michael_Avidan

MVP

Nov 04, 2017

gethyn.edwards,

Try my following suggested formulas:

For GREEN:

=(A1>=EDATE(TODAY(),-12))*(A1<=EDATE(TODAY(),12))

For RED:

=1-(A1>=EDATE(TODAY(),-12))*(A1<=EDATE(TODAY(),12))

----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

SergeiBaklan

Diamond Contributor

Nov 04, 2017

Michael, is it any difference in results compare to MEDIAN?

Michael_Avidan
MVP
Nov 04, 2017
SergeiBaklan wrote:
Michael, is it any difference in results compare to MEDIAN?
Nope (maybe a "bit" shorter).
Sorry, I didn't noticed your MEDIAN suggestion nor did I get an E-Mail notifying that someone added a post in a discussion I participated.
Do you know how to switch-on the option to receive notification E-Mails ?

P.S.
Will you accept the following to be a bit more "ink saver" ?
=A1=MEDIAN(EDATE(TODAY(),{-12,12}),A1)

TXS,
Michael
- SergeiBaklan
  Diamond Contributor
  Nov 04, 2017
  Hi Michael,
  
  Yes, with array constant it's even better.
  As for the notification I'll send you in-private message shortly. No secrets, that's just off-topic for this thread.