Note: this formula assumes Excel's default 1900 date system.
How this formula works
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:
To get the next working day, or next business day, you can use the WORKDAY function. In the example shown, the formula in C4 is: = WORKDAY ( B4 , 1 , holidays ) How this formula works The WORKDAY formula is fully automatic. Given a date and days, it...
To get a date 6 months in the future, on the next work day, you can use a formula based on the WORKDAY function, with help from EDATE. In the example shown, the formula in C6 is = WORKDAY ( EDATE ( B6 , 6 ) - 1 , 1 , B9:B11 ) How this formula works...
The Excel CEILING function returns a given number rounded up to a specified multiple. For example, =CEILING(A1,5) could be used to round a price in A1 to the nearest 5 dollars. CEILING always rounds up.
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.