How to Count Fridays in a Year in Excel

If you want to know how many Fridays fall in a given year using Excel, you’re in the right place. This is useful for HR planning, payroll scheduling, school timetables, or personal curiosity. Excel has built-in date functions that make this count Fridays task simple and efficient.

Key Takeaways

• Use the DATE, WEEKDAY, and SEQUENCE functions to calculate Fridays.
• Combine SUMPRODUCT for an array-based solution without helper columns.
• VBA can offer a flexible and dynamic approach for repeated year analysis.
• Common issues like leap years or incorrect weekday numbers can lead to miscounts.
• This method can be customized for any weekday, not just Fridays.

What Is a Friday Counter in Excel?

This solution counts how many Fridays occur in a specific year. It uses Excel’s ability to generate and evaluate dates programmatically. You define the start and end dates, and then check each date to see if it is a Friday (weekday number 6 in Excel’s system).

Step-by-Step Guide

STEP 1: Enter the year in cell A2 (e.g. 2024).

STEP 2: In cell B1, calculate the start date:

=DATE(A2, 1, 1)

STEP 3: In cell C1, calculate the end date:

=DATE(A2, 12, 31)

STEP 4: Use this formula to count the number of Fridays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B2&":"&C2)))=6))

• It processes an array of dates generated by ROW(INDIRECT(...))

• Applies a condition to each (WEEKDAY(...) = 6 for Friday)

• Converts TRUE/FALSE to 1/0 using --

• Sums them using SUMPRODUCT

Common Mistakes and Tips

Incorrect weekday number: Make sure you know Excel’s weekday system. Sunday is 1 by default, Friday is 6.

Not using array-friendly functions: ROW(INDIRECT(...)) works only if you’re evaluating a range of sequential dates. If you input invalid date ranges, the formula breaks.

Year not specified as a full year: Use full 4-digit year values to avoid errors in DATE calculations.

Regional settings: Some regional settings shift the weekday numbering. Test =WEEKDAY("2024-01-05") to verify.

Leap year miscount: Leap years add an extra day. This affects total count logic if you try to shortcut the method.

Bonus Tips (Advanced Methods)

Dynamic dropdown for year:
Add a dropdown list using Data Validation referencing a list of years. The formulas will auto-adjust.

Power Query alternative:
Use Power Query to generate a list of dates from January 1 to December 31, then filter for weekday = Friday, and count rows.

VBA Option:

Function CountFridays(yearVal As Integer) As Integer
    Dim dt As Date
    Dim count As Integer
    dt = DateSerial(yearVal, 1, 1)
    Do While Year(dt) = yearVal
        If Weekday(dt, vbSunday) = 6 Then count = count + 1
        dt = dt + 1
    Loop
    CountFridays = count
End Function

Use this in a module and call =CountFridays(2024) in any cell.

Use Cases

Payroll Scheduling: Companies that pay employees every Friday can forecast how many pay periods will occur in a year.

Event Planning: If you organize weekly events on Fridays, you can estimate how many events to schedule annually.

School Timetabling: Schools that rotate activities on specific weekdays can build calendars accurately.

Retail Promotion Planning: Retailers often plan Friday-based deals; knowing the count can guide budget planning.

FAQs

What weekday number is Friday in Excel?
It’s 6 if Sunday is set as the start of the week.

Can I modify this to count Mondays?
Yes, just change the =6 condition to =2.

Does the formula work for leap years?
Yes, it will include February 29 if the year is a leap year.

What if I want to count Fridays between two dates?
Change the start and end date in the formula to custom dates instead of full year range.

How can I visualize the Fridays?
You can use conditional formatting to highlight Fridays on a calendar template.

Summary

Using Excel to count how many Fridays occur in a year is a practical task for planning, reporting, and scheduling. With a combination of formulas or a custom VBA function, you can do this in seconds. The method is flexible and can be adapted for any weekday or custom period.