Note: this formula assumes Excel's default 1900 date system.
This formula depends on the CELING function, which rounds numbers up to a given multiple. It works because how dates work in Excel's default 1900 date system, where the first day in the system is the number 1, equal to the date Sunday January 1, 1900.
In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturday's in the future are evenly divisible by 14.
The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous.
The other every other Friday
If you need to get the alternate Friday in an every other Friday scheme, you can use this version of the formula:
The WORKDAY formula is fully automatic. Given a date and days, it will add days to the date, taking into account weekends and, optionally, holidays. In this case, holidays are supplied as the named range holidays (E4:E8), so holidays are taken into...
Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015. Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.