Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating times from day to day (Excel 2002)

    Is there a way to calculate a difference between two different times on two different days and give the result in minutes.

    My client is looking to calculate time from admittance to hospital until time that a certain drug is administered. If it's the same day, no problem, but since the date field is separate from the time field - how would we factor in the date field into a formula for something like this.

    --*Rob

  2. #2
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating times from day to day (Excel 2002)

    You lost me there...what formula did you use to get the result of 750?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating times from day to day (Excel 2002)

    Sorry, meant to post that as a formula:

    =C1+D1-A1-B1
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating times from day to day (Excel 2002)

    If the date and time fields are valid Excel date and time fields (not text masquerading as time or date), <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>1</td><td align=right>01/01/04</td><td align=right>9:00 PM</td><td align=right>01/02/04</td><td align=right>9:30 AM</td><td align=right>=C1+D1-A1-B1</td></table>and the difference calculation in cell E1 is Custom formatted as [m]:ss. Does this help?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating times from day to day (Excel 2002)

    Chip Pearson has the best collection of Date Time information.

    www.CPearson.com
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating times from day to day (Excel 2002)

    I'd be a little careful with John's formula in case there are dates and times buried in the displayed values. I would use:

    =int(c1)+(d1-int(d1))-int(a1)-(b1-int(b1))

    Just to make sure that WYSIWYG.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating times from day to day (Excel 2002)

    Or =INT(C1)+MOD(D1,1)-INT(A1)-MOD(B1,1)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating times from day to day (Excel 2002)

    Excel keeps dates and times togeter. The date is the whole number part. The time is the fractional part.
    I would go with John's original formula, but multiply by 24*60 to convert the fraction to minutes:
    <pre>=24*60*(C1+D1-A1-B1)</pre>


    The safe way that John just posted uses the int function to make sure there is no hidden time, and uses the mod function to make sure there is no hidden date. I've attached a worksheet in case you are still confused. Date and times are tough at first. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating times from day to day (Excel 2002)

    Sammy, the OP stated that the date and time fields were separate records, so I took that at face value. And Chipshot gets the credit for the safer approach of using INT and MOD to ensure that dates are only dates and times are only times.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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