# Time Ranges

1. ## Time Ranges

Hello,

My work sheet contains occurances that take place at certain times of the day. My spreadsheet contains 2000 rows. Each row is an occurance. Each occurance has a particular time stamp associated with the occurance.

I'm attemtping to evaluate if the time of day falls into a time range. When I evaluate the time 07:31, this time would fall into the 0700-0800 time range. If the time was 09:31, it would fall into the 0900-1000 time range, etc. Consequently, I need to evaluate each specific time and return the range that this time would fall into.

The time stamp is in column A and there are 2000 rows. I'd like to return the time range "0700-0800" or "0800-0900", or "0900 - 1000" etc in column F

Thanks

2. Try:

=TEXT(A2,"hh")&"00-"&TEXT(A2+"1:00"+0,"hh")&"00"

where A2 houses timestamp

3. This worked. Thanks

How would the formula change if it was simply a number range?

Thanks

4. Originally Posted by NBVC
Try:

=TEXT(A2,"hh")&"00-"&TEXT(A2+"1:00"+0,"hh")&"00"

where A2 houses timestamp

Excelent!

Replying so I can have this one on my CP. I need to have a look at the text function.

My solution included a formula that concatenated 24 different if statements...

5. Couldn't the formula be shortened to =TEXT(A2,"hh")&"00-"&TEXT(A2+1/24,"hh")&"00"?

6. Originally Posted by jmag
This worked. Thanks

How would the formula change if it was simply a number range?

Thanks
What exactly do you mean?

##### Users Browsing this Thread

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

#### 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