# Change time range for functions

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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?  Register To Reply

4. ## 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)  Register To Reply

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