+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,138

    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"

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    6

    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: copy to clipboard
    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.
    Last edited by Hidden_Gecko; 02-06-2013 at 09:36 PM.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,138

    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. #5
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    6

    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: copy to clipboard
    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?
    Last edited by Hidden_Gecko; 02-06-2013 at 10:13 PM.

  6. #6
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    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.
    Attached Files Attached Files

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,138

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

    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)

  8. #8
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #9
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    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. #10
    Registered User
    Join Date
    02-06-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #11
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

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

    You're Welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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