Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Count fridays between two dates

1. ## Count fridays between two dates

Hi all,

In column A I have date 1, in B I have date 2. In C I want to count the number of fridays between the two dates. I know how to count the number of days, but /7 won't work. Is there a way to count the number of fridays (or other days of the week) between two dates?

In this Klaverjassers I have made a graphic over a time scale. But when there's no data yet (in the future) the graphic assumes the values are zero, so the lines run back to zero. No big deal, but it's plain ugly, so can I stop the lines from running back to zero and just let them 'stop' today?

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Count fridays between two dates

You could simply divide by 7 to establish the number of complete weeks. Then for working out the remainder number and the weekday of the start date you should be able to deduce how many Fridays.

Use the weekday function for the later bit. A simple formula should be easy from there. if you still struggle I can write later today.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Count fridays between two dates

Thanks for helping, but I'm afraid I need a bit more guidance. I've struggled around with the weekday function, but I can't figure out how to use it for this purpose. I've tried:

=IF((WEEKDAY(A2)*(WEEKDAY(B2)))<36;ROUND.DOWN(((B2-A2)/7);0);ROUND.UP(((B2-A2)/7);0))

A2 = 12-5-2005
B2 = 8-6-2005
Result = 3

I know it's not the right way to count fridays, but am I on the right track?
Haven't looked into the graphic yet...

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Count fridays between two dates

Bump

I've also tried:

=IF((WEEKDAY(A2))<6&(WEEKDAY(B2))<6;ROUND.DOWN(((B2-A2)/7);0);ROUND.UP(((B2-A2)/7);0))

Or:

=IF(B2="";"";IF((WEEKDAY(A2))<6&(WEEKDAY(B2))<6;ROUND.DOWN(((B2-A2)/7);0);ROUND.UP(((B2-A2)/7);0)))

(to get an empty cell if B2 is empty) which works most of the time, but not always. Anybody got a better idea?

Edit: Again, this klaverjassers is the sheet I'm working on.
Last edited by ukelele; June 29th, 2005 at 04:34. Reason: Added other formula and link to sheet.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Count fridays between two dates

Bump

Now I'm trying the NETTO.WORKDAY function. I've made a list of all non-fridays that I can specify as vacation days in Data!G:G. and in Data!I I put:

=IF(Input!B2="";"";IF((WEEKDAY(Input!B2))>=6;(NETTO.WORKDAYS(Input!A2;Input!B2;\$G\$2:\$G\$129)-1);NETTO.WERKDAYS(Input!A2;Input!B2;\$G\$2:\$G\$129)))

It looked promising, but it still produces some errors. Any help would be greatly appreciated!

6. ## Re: Count fridays between two dates

Hi ukelele,

This formula works on the basis that there will always be 1 Friday for every complete 7 days between the 2 dates, plus 1 if only one of the two dates falls before a Friday.

=IF(OR(N(B1)=0,N(B2)=0),"",INT(DATEDIF(B1,B2,"D")/7)+(WEEKDAY(B1)<6)

The difference between the two dates is divided by 7 and converted to an integer. The section of the formula '(WEEKDAY(B1)<6)' equates to 1 (TRUE) or 0 (FALSE), which is added to the number of complete 7 day weeks.

My 2 dates are in B1 and B2.

What I don't know is how you want to count the number of Fridays if one or both of the dates falls on a Friday. If the two dates fall on consecutive Fridays, DATEDIF will return 7, which will result in 1 Friday being returned, which may not be what you want.

Hope this helps.

Regards,
Batman.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Count fridays between two dates

Thanks a lot Batman!

[q]What I don't know is how you want to count the number of Fridays if one or both of the dates falls on a Friday.[/q]If the first date is an friday I don't want to count it, but if the second is a friday I do want to count it.

I couldn't find the DATEDIF function in my excel version. I'm using the Dutch version, so maybe it's named completely different. But I think I can just use B2-B1 to get the number of days between to dates, can't I? For an empty cel only B2 needs to be empty, so I think I can do without the OR function. Now I'm on this:

=IF((Input!B2)="";"";(IF((WEEKDAY(Input!B2))>=6;INTEGER(((Input!B2-Input!A2)/7)+(WEEKDAY(Input!A2)<6))-1;INTEGER(((Input!B2-Input!A2)/7)+(WEEKDAY(Input!A2)<6)))))

I haven't set all criteria right yet, but it looks promising. I'll keep on trying. Thanks a lot for helping!

8. Senior Member
Join Date
29th December 2004
Location
Philipp
Posts
113

## Re: Count fridays between two dates

without dated if
=INT((+B11-A11)/7)+IF(WEEKDAY(A11)+MOD(B11-A11,7)>=6,1,0)

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Count fridays between two dates

Thanks a lot, but it keeps returning #N/B.

Problem I have is that I have to translate all functions into dutch, which is not allways as straightforward as you might think. I have found a MODUS function, which I think is the one you reffered to as MOD.

I'm now on :
=(INTEGER((+Input!B2-Input!A2)/7))+(IF(((WEEKDAY(Input!A2))+(MODUS((Input!B2-Input!A2);7)))>=6;1;0))

I know I use excessive ( and ) but that makes it easier for me to keep things apart. What could I have done wrong?

Dates are in Input!A2 and Input!B2 of course.
Last edited by ukelele; June 29th, 2005 at 23:49. Reason: translated functions

10. ## Re: Count fridays between two dates

Hi Ukelele,

I have tried your latest version, converted into English, with all the brackets you use, and it works for me.

Can you try highlighting individual sections of the formula that should by themselves equate to a value and press F9 to see that value. That way you should be able to find out which part of the formula is erroring.

Regards,
Batman.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno