Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Count fridays between two dates

  1. #1
    Join Date
    27th June 2005
    Posts
    27

    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?

    Additional Question:
    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?

    Thabks in advance!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th June 2005
    Location
    Lincolnshire, UK
    Posts
    1,137

    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. #3
    Join Date
    27th June 2005
    Posts
    27

    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. #4
    Join Date
    27th June 2005
    Posts
    27

    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. #5
    Join Date
    27th June 2005
    Posts
    27

    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!
    Mr. President, we cannot allow a mineshaft gap!
    -General "Buck" Turgidson-

  6. #6
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    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. #7
    Join Date
    27th June 2005
    Posts
    27

    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!
    Mr. President, we cannot allow a mineshaft gap!
    -General "Buck" Turgidson-

  8. #8
    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. #9
    Join Date
    27th June 2005
    Posts
    27

    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
    Mr. President, we cannot allow a mineshaft gap!
    -General "Buck" Turgidson-

  10. #10
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Dates & String Dates After Given Date
    By p45cal in forum EXCEL HELP
    Replies: 4
    Last Post: May 14th, 2008, 21:43
  2. Replies: 4
    Last Post: May 14th, 2008, 11:53
  3. Count Dates By Month
    By lfm in forum EXCEL HELP
    Replies: 1
    Last Post: January 8th, 2008, 02:23
  4. Count Dates Within Last x Days
    By Echo Delta in forum EXCEL HELP
    Replies: 5
    Last Post: September 7th, 2007, 11:23
  5. Count Of Dates Between Two Dates
    By texaspete in forum EXCEL HELP
    Replies: 2
    Last Post: October 4th, 2006, 19:35

Bookmarks

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