# Help taking a time from one field and outputting a time period in a different column

1. ## Help taking a time from one field and outputting a time period in a different column

Hi all,

Firstly, thank you all in advance for taking the time to even look at this, I am by no means an expert when it comes to using Excel.

I'm looking to collect information regarding the number of things that happened during a specific time period - for example, if I had an entry that has the a time of 12:01:05, I want to have a field that designates that as 1200-1300 (and to have the same effect for any time between 12:00:00 to 12:59:59).

My spreadsheet has a datetime, time, date, day, and this time period (0000-0100, 0100-0200 and so on up to 2300-0000) column.

From the datetime column I've managed to split the information automatically into separate instances of both date and time, and the day of the month is in turn calculated automatically from the date column.

So it's just this last time period column that I could do with help on, if there is even a way!

Thanks again.

2. ## Re: Help taking a time from one field and outputting a time period in a different column

hi Hidden_Gecko, welcome to the forum. assuming data in A1, maybe:
=HOUR(A1)&"00-"&HOUR(A1)+1&"00"

3. ## Re: Help taking a time from one field and outputting a time period in a different column

That works perfectly for 1000-1100 right the way through to 2200-2300. which is brilliant, thanks!

Do you know if there's a way to make the HOUR express as a double digit regardless of what hour it is?

Otherwise, for 0000-0100 through to 0800-0900 I'd have to do
Formula:
`Please Login or Register  to view this content.`

Then I'd just do 0900-1000 and 2300-0000 manually because they'd require their own slightly different formula again, and for the sake of the individual entries I guess there's little point really.

4. ## Re: Help taking a time from one field and outputting a time period in a different column

you're very welcome~~ ahh i missed that out eh
=TEXT(HOUR(A1),"00")&"00-"&TEXT(HOUR(A1)+1,"00")&"00"

never settle for manual work in Excel! =)

5. ## Re: Help taking a time from one field and outputting a time period in a different column

You're a godsend!

Last thing I did was change the last hour to this
Formula:
`Please Login or Register  to view this content.`

To make the last period express as 2300-0000 instead of 2300-2400, it's a tiny bit more work for me but that's fine, you've saved me a ton of time already. Thank you so much!

Last question: it's on the same spreadsheet I'm making, but it's not really related to the original question..
Now I have all my outputs on this sheet, I need to collate them - total for each day and period over the course of the month.

So I have 9 entries that appeared on Saturdays for the 2200-2300 time period.

I can count them manually, but for the 860 entries I have for the entire month it seems like there might just be a quicker way.

Here's part of the information I have on a sheet titled "RAW Data" - "End Time" is A1, if that helps!

End Time--------Time--------Date-----Day---Time Period
14/09/2012 00:28 00:28:59 14/09/2012 Friday 0000-0100
28/09/2012 00:34 00:34:00 28/09/2012 Friday 0000-0100
07/09/2012 00:52 00:52:03 07/09/2012 Friday 0000-0100
28/09/2012 00:55 00:55:00 28/09/2012 Friday 0000-0100
14/09/2012 01:23 01:23:10 14/09/2012 Friday 0100-0200
28/09/2012 01:25 01:25:14 28/09/2012 Friday 0100-0200
14/09/2012 01:31 01:31:57 14/09/2012 Friday 0100-0200
14/09/2012 01:52 01:52:16 14/09/2012 Friday 0100-0200
28/09/2012 02:14 02:14:12 28/09/2012 Friday 0200-0300
28/09/2012 02:18 02:18:27 28/09/2012 Friday 0200-0300
07/09/2012 03:19 03:19:37 07/09/2012 Friday 0300-0400
07/09/2012 03:29 03:29:33 07/09/2012 Friday 0300-0400

I need to enter the number of times each time period appears on the given day on a sheet titled "Compiled Data" which looks like this

Day--------0000-0100 0100-0200 0200-0300 0300-0400
Friday----------4 ----------4-----------2----------2

If you understand at all what I need to do, do you think there's a way to do it?

6. ## Re: Help taking a time from one field and outputting a time period in a different column

Here's an example of what I've done up to now.

7. ## Re: Help taking a time from one field and outputting a time period in a different column

=TEXT(HOUR(A2),"00")&"00-"&IF(HOUR(A2)=23,"00",TEXT(HOUR(A2)+1,"00"))&"00"

and in "Compiled Data", ensure your days in column A is the same with "RAW data" sheet. currently, your days have an extra space behind. after doing the cleanup, try this in B2:
=SUMPRODUCT(('RAW data'!\$D\$2:\$D\$860=\$A2)*('RAW data'!\$E\$2:\$E\$860=B1))

if you have managed to upgrade to Excel 2007, then:
=COUNTIFS('RAW data'!\$D\$2:\$D\$860,\$A2,'RAW data'!\$E\$2:\$E\$860,B1)

8. ## Re: Help taking a time from one field and outputting a time period in a different column

You are a genius and I am in your debt.

Would that I had more reputation to give!

9. ## Re: Help taking a time from one field and outputting a time period in a different column

Try this one...

Shorter and more elegant

=TEXT(B2,"hh\0\0-")&TEXT(B2+1/24,"hh\0\0")

10. ## Re: Help taking a time from one field and outputting a time period in a different column

Excel blows my mind with how flexible it is. Two very different looking formula to produce the same output.

Thanks for the insight!

11. ## Re: Help taking a time from one field and outputting a time period in a different column

You're Welcome!

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