CODEWITHSUNDEEP
excel
Kyle
Kyle

Reputation: 1173

Conditionally highlighting future date based on initial date value

I'm new to Excel (and Spreadsheeting in general), and am trying to harness the power of conditional formatting to highlight dates occurring every two weeks after a set date.

I have a fixed date value, and its serial:

# first occurrence of year, highlight today and every two weeks after
date: 01/05/2018
serial: 43105

Now, in my spreadsheet- I have a matrix of serial values in grid format for each month (i.e. full year calendar view).

I have a formula to get the first Sunday for each month. So serials are as follows:

# jan
date: 01/01/2018
serial: 43101

# feb
date: 02/01/2018
serial: 43129

I have a conditional rule to highlight the first occurrence, but am not sure how to implement the logic for occurrences every 14 days after the initial and thereafter.

My pseudo-code

for (each day in year, day) {
    if (day == specialDate) {
        // highlight cell, and update initialDate to this 
        highlightCell()
        specialDate = day + 14;
    }
}

Upvotes: 0

Views: 95

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5195

I would use modulus (Excel MOD function).

E.g. something like:

= MOD(<input date>-<reference date>,14)=0

If you use a formula like this for your conditional formatting rule, it will apply the formatting rule only to a those dates that are exact integer multiples of 14 days away from the <reference date> in the formula above.

Upvotes: 1

Related Questions