Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Counting Weeks With Date Time Spans

  1. #1
    Join Date
    24th January 2007
    Posts
    2

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    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

  4. #4
    Join Date
    6th July 2006
    Location
    Ireland
    Posts
    40

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    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)
    VB:
    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 at 02:08.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2007
    Posts
    2

    Re: Counting Weeks With Date Time Spans

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

    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. Replies: 6
    Last Post: May 27th, 2008, 05:10
  2. Counting Weeks And Days
    By Gary Jon Davis in forum EXCEL HELP
    Replies: 3
    Last Post: August 1st, 2007, 06:45
  3. Date Spans In Select Case
    By mattw in forum EXCEL HELP
    Replies: 9
    Last Post: July 25th, 2007, 07:55
  4. Calculate Time Spans With Constraints
    By Hellsdiver in forum EXCEL HELP
    Replies: 13
    Last Post: December 30th, 2006, 18:33
  5. Counting weeks from dates
    By Gogo in forum EXCEL HELP
    Replies: 3
    Last Post: December 29th, 2005, 19:43

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