Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: date spreadsheet

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to calculate the number of weeks from one date to another (ie how many weeks are there between 3/03/01 thru 8/1/02)

    Does anyone know a formula?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might want to try using the following formula:

    =(B1-A1+1)/7

    where B1 = end date, A1 = start date.

    HTH

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will break-out a full date:

    =IF(F24="","Please Enter Your Date Above!",DATEDIF(F24,NOW(),"y")&" years, "&DATEDIF(F24,NOW(),"ym")&" months and "&DATEDIF(F24,NOW(),"md")&" days")

    Note: You need the analyze addin that comes with Excel installed to work it.

    The date data is in "F24" the formula can be in any cell. You can strip out the other parts if you need to. JSW

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    And in the style of the above:

    =IF(OR(F13="",F16=""),"Add Both Dates Above!",DATEDIF(F13,F16,"y")&" years, "&DATEDIF(F13,F16,"ym")&" months and "&DATEDIF(F13,F16,"md")&" days")

    Will give the between age. JSW

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What does this tell me?

    end date - beginning date +1 divided by 7

    What does the one signify???

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    The + 1 means the formula will include the start date as well.

    For example, start date = 1/4/02, and end date = 2/4/02. If just use end - start then the answer is 1, but in fact there are 2 dates in between.

    That is the reason for + 1.

    HTH

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Attention Joe Was

    Re Datedif You stated "Note: You need the analyze addin that comes with Excel installed to work it. "

    Datedif is a regular function; however, it was only documented in Excel 2000


  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unlike Datedif, VBA's Datediff has a Week parameter.

    With VBA, I created a User Defined Function (UDF) named Weeks . Use it as follows
    =Weeks(A4,B4)

    The UDF is created in a regular VBA module; you can copy the following into the module.

    Function Weeks(dFirstDate, dSecondDate) As Integer
    Application.Volatile
    Weeks = DateDiff("ww", dFirstDate, dSecondDate)
    End Function



    [ This Message was edited by: Dave Patton on 2002-03-28 11:07 ]

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave:
    Your UDF DateDiffW works -- beautiful!

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Joe Was, Dave Patton & Yogi Anand

    I AM SO CONFUSED??

    What is a VBA and a UDF??


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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