Announcement

Collapse
No announcement yet.

Count fridays between two dates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #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.
    Rich

    www.imits.co.uk - for all your training and development needs!

    Comment


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

      Comment


      • #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, 04:34. Reason: Added other formula and link to sheet.
        Mr. President, we cannot allow a mineshaft gap!
        -General "Buck" Turgidson-

        Comment


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

          Comment


          • #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.
            Regards,
            Batman.

            Comment


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

              Comment


              • #8
                Re: Count fridays between two dates

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

                Comment


                • #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, 23:49. Reason: translated functions
                  Mr. President, we cannot allow a mineshaft gap!
                  -General "Buck" Turgidson-

                  Comment


                  • #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.
                    Regards,
                    Batman.

                    Comment


                    • #11
                      Re: Count fridays between two dates

                      Hi Batman,

                      That F9 tip is a good one, I didn't know that function yet.
                      It's the MODUS part that is giving me trouble:

                      (MODUS((Input!B2-Input!A2);7))=#N/B
                      (Input!B2-Input!A2)=8

                      I don't fully understand the MODUS function yet. I think it finds the number that occurs the most in the given list of numbers. In a list of 8;7 that could go wrong.

                      Thanks again,
                      Bart
                      Mr. President, we cannot allow a mineshaft gap!
                      -General "Buck" Turgidson-

                      Comment


                      • #12
                        Re: Count fridays between two dates

                        Unfortunately, I can't help you there. MODUS is not a function that exists in the English version of Excel, so I don't know whether it equates to the MOD or MODE function.

                        You need the function that equates to MOD, which calculates the remainder after 1 number is divided by another [hence, MOD(8,7) returns 1].

                        Regards,
                        Batman.
                        Regards,
                        Batman.

                        Comment


                        • #13
                          Re: Count fridays between two dates

                          I was having a problem with working out the number of Fridays between two dates and WillR gave me this solution.

                          Put this macro into a module in your workbook -

                          Code:
                          Function WEEKDAYCOUNT(StartDate As Date, EndDate As Date, MyWeekday As Integer)
                               'Counts the number of specified weekdays between a
                               'start & ending date
                              Dim x As Long
                              x = 0
                              For i = StartDate To EndDate Step 1
                                  If Weekday(i, vbMonday) = MyWeekday Then
                                       'vbmonday = monday is 1 thru sunday = 7
                                       'i.e. if MyWeekDay = 2 will count the number
                                       'of Tuesday's btwn Start & End Date
                                      x = x + 1
                                  End If
                              Next i
                              WEEKDAYCOUNT = x
                          End Function
                          Then go sheet1 in your workbook

                          Put your start date in D3

                          Your end date in E3

                          Then this calculation in F3

                          Book1.xls!WEEKDAYCOUNT(Sheet1!D3,Sheet1!E3,5)

                          It will calculate the number of Fridays between your start and end date.

                          Comment


                          • #14
                            Re: Count fridays between two dates

                            Hi,

                            Another option..

                            =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&A3)))=6))
                            where A2 houses start date and A3 houses end date.
                            Kris

                            ExcelFox

                            Comment


                            • #15
                              Re: Count fridays between two dates

                              Hi all,

                              Thanks a lot for all your idea's. I will try to implement them, but the macro look quite daunting to me. I'll give it a shot though. I've come this far so why not a bit further ;-)
                              I'm afraid I'll have to input the data manualy nonetheless, because I've realised that actualy it's not the really the number of friday's I'm after, it's somewhat more complex. I'd have to include a whole range of IF-commands and even then there would but some exceptions. Nothing beats the human brain.

                              Thanks a lot for your willingness to help a complete stranger.
                              Bart.
                              Last edited by ukelele; July 2nd, 2005, 06:15. Reason: typo
                              Mr. President, we cannot allow a mineshaft gap!
                              -General "Buck" Turgidson-

                              Comment

                              Working...
                              X