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"

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.

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! =)

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?

missed out on your 2300-0000 earlier. use this instead:
=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)