September 8, 2016
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.
If 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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.
To 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.
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...
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...
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).
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.
|"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