Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Counting Weeks With Date Time Spans

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

  • Counting Weeks With Date Time Spans

    I need to make formulas to count the number of weeks that each phase of a project takes, but I currently have the start and end dates of the phases. (See attachment)


    Thanks.
    Attached Files

  • #2
    Re: Counting Weeks With Date Time Spans

    See the attachment. To round the weeks to the next highest number rather than the nearest number, change "ROUND" to "ROUNDUP" in the equations in row 4.
    Attached Files
    .

    Comment


    • #3
      Re: Counting Weeks With Date Time Spans

      Can you just subtract the two dates then divide by 7 ?? You could use ROUNDUP in conjunction to ensure the number of weeks was rounded up...
      D

      Better a bad day on the water than a good day in the office

      Comment


      • #4
        Re: Counting Weeks With Date Time Spans

        Hello!

        Based on the cells in your document, try this:

        =((YEAR(F3)-YEAR(E3))*52)-(WEEKNUM(E3)-WEEKNUM(F3))+1

        This takes the weeknumber of the date and do the calculation on that. That would mean that if both dates would be in the same week the result would be 0, therefore the +1 in the end.

        Im not sure what you mean by "Count the weeks", thats what we do in the row underneath where you write "Sum the weeks".

        This might not work so great overlapping several years though. Some weeks have 53 weeks (although just a few days week 53), and I'm always counting on 52 weeks no matter what.

        Kind regards
        /Henrik

        Comment


        • #5
          Re: Counting Weeks With Date Time Spans

          DC, you should decide what rounding if any is needed. The rounding options are:
          1. Up - use =Ceil() or =Roundup
          2. Down - use =Floor()
          3. Standard Rounding - =Round()
          4. Whole weeks only (like 2) using a UDF as shown below.
          Use the UDF like this where A3 is the beginning date and B3 is the ending date:
          =NumWeeks(A3,B3)
          Code:
          Function NumWeeks(Date1 As Date, Date2 As Date) As Integer
            NumWeeks = DateDiff("ww ", Date1, Date2)
          End Function
          Last edited by Kenneth Hobson; March 21st, 2007, 02:08.

          Comment


          • #6
            Re: Counting Weeks With Date Time Spans

            Thanks so much to everyone. I really appreciate it, especially as a first time poster!

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X