Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (Excel 2003)

    I want to create a formula that will give me the number of weeks, months, days, hours, minutes and seconds between two dates and times. eg. 1Mar05 (being today's date) and 4 July 05 (event date) and 3:00 pm (event time). Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Formula (Excel 2003)

    Do you mean:
    A) There are 4 months, 0 weeks, 3 days and 15 hours between 1 March 05 midnight and 4 July 05 3 PM
    or
    [img]/forums/images/smilies/cool.gif[/img] There are 4 months between 1 March 05 and 4 July 05, or 17 weeks, or 125 days, or 3000 hours etc.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 2003)

    Thanks for your prompt reply. I don't think I really explained it properly. What I want is a formula to count down to an event to be able to say how many months, weeks, days and hours to an event. I have already look at doing the same as sample ([img]/forums/images/smilies/cool.gif[/img]. I hope you can understand what I want?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Excel Formula (Excel 2003)

    See the attached workbook. The worksheet is protected, so that you can only enter the date/time of the event. If you want to have a look at the formulas, or modify the date and time formats used, select Tools | Protect | Unprotect Sheet.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (Excel 2003)

    If the date/time to count down to is in cell A1, does this formula give you what you want:

    <pre>=TEXT(DATEDIF(NOW(),A1,"m"),"0")&" Months "&
    TEXT(INT(DATE(YEAR(A1-NOW()),1,DAY(A1-NOW()))/7),"0")&" Weeks "
    &TEXT(MOD((DATE(YEAR(A1-NOW()),1,DAY(A1-NOW()))),7),"d")&" Days "&
    TEXT(MOD(A1-NOW(),1)*24,"0")&" Hours"
    </pre>


    That formula all goes on one line. See the attached file.
    Legare Coleman

Posting Permissions

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