FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003

September 8, 2016

 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Thanks for joining me today for my 'Excel in Minutes' tip.

Please feel free to share it with your friends—chances are they will too.

I
f you missed my 'Excel in Seconds' newsletter from Tuesday, I showed you a tip for 'Setting the Print Area for Multiple Nonadjacent Ranges'. You can read the tip here.

Keep on
Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com
 

Francis Hayes (TheExcelAddict.com)



If you missed my last newsletter, you can click here to view it online.

\

 
TheExcelAddict.com Quote of the Day

"I would rather be hated for who I am, than loved for who I’m not."

-- Kurt Cobain --

If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Determine if a Date Falls Within a Certain Number of Days of Another Date

In last week's 'Excel in Minutes' tip, I showed you a formula you can use to 'Check if a Date Falls Between Two Other Dates'.

Another similar question I was asked is how to check if a date falls within a certain number of days from another date. As with many things in Excel, there are various ways to do this, but here is one that I think is easy to understand.

T
o do this, we need three arguments for our formula: the date we want to check, the date we want to compare against, and the number of days range.

To calculate the number of days between two dates, we simply subtract the latest date from the earliest date. However, since we may not know which cell will contain the earliest date and which will contain the latest, we can use the ABS (i.e. absolute value) function to ignore the sign (i.e. + or -) so that the number of days result will always be positive. We then compare this number with our target range.

=ABS(A2-B2)<=C2

Check If Date Within Certain Number Of Days in Microsoft Excel 2007 2010 2013 2016 365
CLICK TO DOWNLOAD THIS PRACTICE FILE

In this example, we are checking to see if the date in A2 (31-Aug-2016) is within 30 days (C2) of the date in B2 (08-Sep-2016). Since our formula subtracts the date in B2 from the date in A2, the result is -8. The ABS function in the formula changes the value -8 to positive 8 which we can then compare with the target # of days in cell C2 (i.e. 30). Therefore the result of 8<=30 in this example is TRUE.

Yes or No Option
If you'd prefer to have a Yes or No result using the previous example, you can incorporate the IF function into the formula like this...

=IF(ABS(A2-B2)<=C2,"Yes","No")

Conditional Formatting Option
If instead you would like to highlight the cells with dates that fall within the # of days, you can use the first formula above in a Conditional Formatting rule rather than in a worksheet formula.

1) Select the dates you want to check (i.e. A2:A3);

2) From the Home tab, click Conditional Formatting, New Rule...;

3) In the 'Select a Rule Type' section, click 'Use a formula to determine which cells to format' and enter the formula
=ABS(A2-B2)<=C2 in the formula box...

Conditional Format Formula to Check If Dates Within Number Of Days in Microsoft Excel 2007 2010 2013 2016 365

4) Click the Format... button and choose the formatting you want to use then click OK, OK to close the conditional Formatting dialogs.

If you need to check the dates against one specific date (rather than all cells in column B) and one specific number of days (rather than all cells in column D), use Absolute References in the conditional formatting formula (e.g. $B$2 and $C$2).

Conditional Format Check If Dates Within Number Of Days Absolute Referencing in Microsoft Excel 2007 2010 2013 2016 365

Do you use a different solution for finding if a date falls within a certain number of days of another date? Tell me how you do it.


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630