How to highlight a date close to todays's date

MAP

Active Member
Joined
Mar 22, 2007
Messages
312
Office Version
  1. 2007
Platform
  1. Windows
Hello Excel Gurus,
I need help with my current Excel project. I am a relative BEGINNER using Excel 2003.. with any knowledge of VBA

I have two requests:

1) What FORMULA can I use to have conditional formatting look in Column B for a date (in a column of dates) that is +/- 15 days (or other number in C2) today's date (or other date in C1)?

2) What FORMULA can be used to display that list of names and DOB CHRONOLOGICALLY in column D?

NameDOBReference Date = 16 Aug 2015List of names and DOB
John Doe05 Aug 1970
+/- 20 daysBarak Obama - 04 Aug 1961
Jane Doe06 Nov 1969John Doe - 05 Aug 1970
Sally Mae22 Aug 1976Sally Mae - 22 Aug 1976
Harry Derty15 Jan 2002Joe Shmoe - 01 Sep 1990
I. Emsilly23 Nov 2000
Yuri Diculus19 Mar 2008
Joe Shmoe01 Sep 1990
Barak Obama04 Aug 1961
Joe Biden20 Nov 1942

<tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
NameDOB16/08/1985List of names and DOBnote checking dob's plus/minus 10 years
3650
John Doe05-Aug-700two names are found
Jane Doe06-Nov-690
Sally Mae22-Aug-76122-Aug-76
Harry Derty15-Jan-020formula in c4
I. Emsilly23-Nov-000
Yuri Diculus19-Mar-080=IF(AND(B4>($C$1-$C$2),B4<($C$1+$C$2)),1,0)
Joe Shmoe01-Sep-90101-Sep-90
Barak Obama04-Aug-610
Joe Biden20-Nov-420
output table
122-Aug-76Sally Mae
201-Sep-90Joe Shmoe
3
4
5
6
7
8
9
formula giving 22-aug-76 in col D
=IF(C6=1,B6,"")
formula giving 22-aug-76 in output table
=IF(ISERROR(SMALL($D$4:$D$12,B23)),"",SMALL($D$4:$D$12,B23))
formula giving sally mae in output table
=IF(C23="","",OFFSET($B$3,MATCH(C23,$B$4:$B$12,0),-1))
i hope this is not your homework !!!!!!!!!!!!!!!!!!!!

<tbody>
</tbody>
 
Upvote 0
1) What FORMULA can I use to have conditional formatting look in Column B for a date (in a column of dates) that is +/- 15 days (or other number in C2) today's date (or other date in C1)?

Try selecting B2:B10 and applying the formula below in conditional formatting

PHP:
=(IF($C$1-DATE(2015,MONTH($B2),DAY($B2))<0,DATE(2015,MONTH($B2),DAY($B2))-$C$1,$C$1-DATE(2015,MONTH($B2),DAY($B2)))>15)
Excel Workbook
ABCD
1NameDOB16-Aug-15List of names and DOB
2John Doe05-Aug-70FALSEBarak Obama - 04 Aug 1961
3Jane Doe06-Nov-69TRUEJohn Doe - 05 Aug 1970
4Sally Mae22-Aug-76FALSESally Mae - 22 Aug 1976
5Harry Derty15-Jan-02TRUEJoe Shmoe - 01 Sep 1990
6I. Emsilly23-Nov-00TRUE
7Yuri Diculus19-Mar-08TRUE
8Joe Shmoe01-Sep-90TRUE
9Barak Obama04-Aug-61FALSE
10Joe Biden20-Nov-42TRUE
Sheet13
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B21. / Formula is =(IF($C$1-DATE(2015,MONTH($B2),DAY($B2))<0,DATE(2015,MONTH($B2),DAY($B2))-$C$1,$C$1-DATE(2015,MONTH($B2),DAY($B2)))>15)Abc
 
Last edited:
Upvote 0
First, I would like to thank MARK858 and oldbrewer for their efforts and assistance. Oldbrewer, this was not my homework, but I do feel I need to do some homework and learn how to be as good as you guys with Excel.

I have noticed that the conditional formatting that is supplied by both of you is similar and it does work, but i am a bit intrigued by how oldbrewer did the "Output Table".

How does the formula work? =IF(ISERROR(SMALL($D$4:$D$12,B23)),"",SMALL($D$4:$D$12,B23))

And why do you this formula instead of a simpler MATCH/INDEX? =IF(C23="","",OFFSET($B$3,MATCH(C23,$B$4:$B$12,0),-1))

Your formulas were amazing and they did help me with my project. I will really appreciate your time if you would explain in simple terms how they work. THANK YOU.
 
Upvote 0
I don't understand index...lol

small(A1:a5,4) is the fourth smallest value in a1:a5

if any formula goes wrong it returns n/a or div/o etc

iserror says are you going to give me an error

the if(iserror merely heads it off at the pass and says do something else instead of giving the error
 
Upvote 0
Oldbrewer, i thank you and everyone else immensely for your contribution in helping me learn Excel.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top