Hi,
I have this file that was done by someone. It works if the year is 1900. This function shows in sheet2 (original)
In line 2 of "Sheet2 (2)", B1 and A2, I change the year from 1900 to 2018. then B2:E2 prompt "#NUM!" error.
In January there are 4 Fridays in 1/5, 1/12, 1/19. 1/26.
Please advise
Last edited by Rocky2013; 10-01-2018 at 01:22 AM.
The formula for counting Fridays
=NETWORKDAYS.INTL(B$1,EOMONTH(B$1,0),"1111011")
It works. ThanksOriginally Posted by Phuocam
Perhaps =NETWORKDAYS.INTL(EOMONTH(B$1,-1)+1,EOMONTH(B$1,0),"1111011") would be a better suggestion, this will work with any date in B1, not just the 1st of the month.
Hi, Apply the below formula in B2 and drag it down & across
Formula:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B$1&":"&EOMONTH(B$1,0))),2)=MATCH($A2,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)))
☚ Click ★ just below left if it helps, Boo?ath?
Hi,Hi, Apply the below formula in B2 and drag it down & across
Formula:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B$1&":"&EOMONTH(B$1,0))),2)=MATCH($A2,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)))
There is an issue. The number of Fridays does not much the months (2018) in the calendar.
Please advise.
Hi jason,
Your formula gives incorrect numbers of Fridays. I copied the formula
to B4, then to C4:M4. For example in March, there should ne 5 Fridays, but the formula
gives 4 Fridays. Please see the comparison in row 6 and row 12 in the attachment.
Thanks
Last edited by Rocky2013; 09-30-2018 at 10:12 PM.
See the attachment.
Last edited by Phuocam; 10-01-2018 at 09:20 AM.
The formula is correct for the date in the attachment. March 2009 had 4 Fridays!
You are right. My mistake I did not check the month-year, rhe year should be 2018, not 2009. I change the years.
Now it matches!
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks