+ Reply to Thread
Results 1 to 4 of 4

Change time range for functions

  1. #1
    Registered User
    Join Date
    02-01-2011
    Location
    Eau Claire, Wisconsin
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    2

    Change time range for functions

    I am working on an Excel spreadsheet in Excel 2007 that shows sleep study results. One of my columns shows the times the patient went to bed each night and they range from 9:00 pm to 2:00 am I need to calculate the Average time in bed, the Min time they went to bed, and the Max time they went to bed. The problem is that Excel is looking at this as a 24 hour clock with 11:59 pm being the latest time and 12:00 am being the earliest. I need it to look at 2:00 am as being later than 9:00 pm. Does anyone know of a way to do this? Basically if Excel would look at the time range as 12:00 pm being the earliest and 11:59 am being the latest it would calculate correctly, but I have not been able to figure out how to do this. Any suggestions would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Change time range for functions

    Hi and welcome to the board

    Say A1 contains 16:00 and B1 02:00 ( 24 hrs system) - The general formula for the difference is =(B1-A1 +(B1<A1))

    EDIT maybe post a small sample sheet if you have trouble

  3. #3
    Registered User
    Join Date
    02-01-2011
    Location
    Eau Claire, Wisconsin
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    2

    Re: Change time range for functions

    Thanks for the idea, unfortunately I do not know which cell will be greater, because the information varies for each patient. Is there a way that you can think of that I could format the entire row to view the times as noon being the earliest and 11:59 am being the latest? Or is there a way to incorporate this into the formula for finding the min and/or max of a column filled with different times?

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Change time range for functions

    In the example, A1 is the "in" hour and B1 the "out" hour.
    As you know, XL stores dates as numbers, 1 day being 1. So one hour is 1/24 day

    What the formula does is
    1 Evaluate the diff B1 and A1 ( which is a number smaller then one provided the patient doesn't stay in bed more than 24 hrs)
    2. Add 1 if the "out" hour is smaller than the "in" hour, thus taking care of the fact XL does not allow negative times or durations

    Maybe post a sample of your data as example

    Instead of the formula provided you can also use =mod(b1-a1,1)
    Last edited by arthurbr; 02-08-2011 at 04:49 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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