Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Hour Format (2000)

    Thought I might as well start a new thread, which is a "take-off" from my earlier date format thread. To review, I have a very large (>1,000 rows) spreadsheet, two columns of which contain downloaded text that represents beginning and ending dates of a certain process. The dates are in the format "yyyy/mm/dd/hh". With the excellent help of several of you I have been successful in converting the date (but not the hour) so as to compute the number of days between the beginning and ending dates. However, to date I have been unsuccessful in my attempts to do the same for the hours. That is, I would like to "reformat" the downloaded hour (in text) into both a 24-hour (i. e., 1-24) and an AM/PM format.
    I attach the file for your analysis.
    Any help would be appreciated.
    Thanks in advance,
    Jeff
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Like this??
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Thanks, Jan, it works great. See also Legare's reply to my previous thread.
    Jeff

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Hour Format (2000)

    Same one but with custom format to show hours with leading zeros if appropriate e.g. "03:00 AM " etc.

    zeddy
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    One last question-how can I compute the total elapsed time in days and hours. For example, if the process begins on 11/1/2001 at 10:00 AM, and ends on 11/5/2001 at 4:00 PM, the total elapsed time would be 4 days and 6 hours. I would like to be able to show this in a single cell "4 days and 5 hours" as well as in 2 cells, one for the elapsed days ("4" in my example) and the other for the elapsed hours in excess of the elasped days ("6" in my example)
    Thanks,
    Jeff.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    If the start date/time is in A1 and the end is in B1, then this formula will give you the days and hours:

    <pre>=INT(B1-A1)&" Days and "&((B1-A1)-INT(B1-A1))*24&" Hours"
    </pre>


    This formula will give you the days:

    <pre>=INT(B1-A1)
    </pre>


    This one the hours:

    <pre>=((B1-A1)-INT(B1-A1))*24
    </pre>

    Legare Coleman

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Legare,
    I'm a novice here, but I don't believe your solution will help me here insofar as a "one-cell" solution is concerned given the different type of original date.
    Take a look at the attached and you will see I think I have solved my problem as far as setting the days and hours out in separate columns, but when I tried your formula for the one-cell solution, it didn't seem to work.
    Thanks,
    Jeff
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    The attached sheet shows how I would do this. I have changed all of the columns C through K.
    Attached Files Attached Files
    Legare Coleman

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Thanks, Legare; works great!

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Oops!
    Legare, shouldn't the "Elapsed Hours" in J4 and J11 be the same as those in K4 and K11?
    Jeff

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Hour Format (2000)

    Thats a rounding issue.

    As a variation, what about using this formula in [K4]
    =I4& " Days and "&TEXT(J4/24,"H")& " Hours"

    ..but I'm sure Legare will follow up with the best solution. She is awesome! Hats off to Legare!

    zeddy

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Zeddy-that cures it! One last Q-any way to reformat the actual numbers themselves in column K so they conform to "Accounting" format with zero decimal places?
    Thanks again,
    Jeff

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    Yes they should. I fixed the formulas in J and K to round like they should.
    Attached Files Attached Files
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    I'm not sure what you are asking here. It looks to me like the numbers in column K already have zero decimal places.
    Legare Coleman

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hour Format (2000)

    What I meant to say was could the zeroes be displayed as "-" as opposed to "0"?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •