How to calculate the number of Fridays so far this year...?

[CatMadJulie]

Hi,

I always struggle with dates and I'm having difficulty working out how many Fridays there are to date in the current year.

I have a weekly paying tenant who regularly skips payments (due on Fridays) and then sometimes pays extra to catch up but I'm always having to calculate the arrears and chase up when it falls behind. It would be great if I could have a formula to count how many rent due days there have been so far to date this year so I can then multiply that by the weekly rental amount and compare that against the rent payments actually received. Any help would be much appreciated.

 

[shg]

	A​	B​	C​	D​
1​	From​	To​	Fridays​
2​	Fri 01 Jan 2016​	Sat 01 Oct 2016​	40​	C2: =SUM(INT((WEEKDAY(A2 - 6) + B2 - A2)/7))

 

[Dave Patton]

another option

Excel 2010
	A	B
2	1-Jan-16	40
1c

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&TODAY())))=6))

 

[CatMadJulie]

Thanks to both of you. Very much appreciated and will make things much easier for me.

 

[shg]

You're welcome.

 

[chicagocomputerclasses]

Here is a fully dynamic version

<table cellpadding="4px" border="1" cellspacing="1">
<tr><td bgcolor="#DFE3E8"></td><td align="center" width="168" bgcolor="#DFE3E8">F</td><td align="center" width="76.8" bgcolor="#DFE3E8">G</td></tr>
<tr><td bgcolor="#DFE3E8">26</td><td bgcolor="#FFFFFF">So far this year</td><td bgcolor="#FFFFFF">=NETWORKDAYS.INTL(DATE(YEAR(TODAY()),1,1),TODAY(),"1111011")</td></tr>
<tr><td colspan="3"></td></tr>
<tr><td colspan="3" bgcolor="#D7E7F9">Sheet1</td></tr>
</table>