+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Minutes and Hours (Interval Measurement)

  1. #1
    Registered User
    Join Date
    05-07-2010
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Minutes and Hours (Interval Measurement)

    I have created a spreadsheet to find the difference between two time intervals...for example when something was received (date and time) and when it was sent out (date and time).

    Assuming:

    A2 = 8/15/2010
    B2 = 8:00AM
    C2 = 8/16/2010
    D2 = 10:00AM

    In my Excel spreadsheet column A is date in, column B is time in, column C is date out, column D is time out, finally column E is the difference in text version as follows:

    E2=C2-A2-(B2>D2)&" days "&TEXT(1+D2-B2,"hh:mm")

    In column F, I want to be able to translate column E into minutes only or hours only so I used the following formula for hours:

    F2=IF(C2-A2>0, TEXT(1+D2-B2,"[h]:mm") & " hour(s)", TEXT(D2-B2,"[h]:mm") & " hour(s)")

    Or using minutes only: F2=IF(C2-A2>0,TEXT((1+D2-B2)*60, "[h]:mm") & " minute(s)", TEXT((D2-B2)*60,"[h]:mm") & " minute(s)")

    My problem arises when it is the same time/day…for some reason it just states 24 hours instead of 0. Assuming the following:

    A2=8/15/2010
    B2=2: 00PM
    C2=8/15/2010
    D2=2:00PM

    I would really appreciate if anyone could tell me why this is happening/ how to solve this…or if there is a better equation to use! Could you please write out the formula? Thank you!

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Minutes and Hours (Interval Measurement)

    You can just use =C2+D2-A2-B2, then format the cell as d" days, "hh:mm for E2. F2 can be =E2 and formatted as [hh]:mm" hour(s)" or [mm]" minute(s)"
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Minutes and Hours (Interval Measurement)

    Quote Originally Posted by darkyam View Post
    You can just use =C2+D2-A2-B2, then format the cell as d" days, "hh:mm for E2.
    As long as the duration is always < 32 days.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1