Results 1 to 15 of 18
Thread: Hour Format (2000)

20020403, 11:16 #1
 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 "takeoff" 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 24hour (i. e., 124) and an AM/PM format.
I attach the file for your analysis.
Any help would be appreciated.
Thanks in advance,
Jeff

20020403, 12:09 #2
 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??
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020403, 12:50 #3
 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

20020403, 14:14 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20020403, 16:02 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Hour Format (2000)
One last questionhow 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.

20020403, 16:58 #6
 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(B1A1)&" Days and "&((B1A1)INT(B1A1))*24&" Hours"
</pre>
This formula will give you the days:
<pre>=INT(B1A1)
</pre>
This one the hours:
<pre>=((B1A1)INT(B1A1))*24
</pre>
Legare Coleman

20020403, 17:18 #7
 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 "onecell" 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 onecell solution, it didn't seem to work.
Thanks,
Jeff

20020403, 22:07 #8
 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.
Legare Coleman

20020404, 07:36 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Hour Format (2000)
Thanks, Legare; works great!

20020404, 08:03 #10
 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

20020404, 08:49 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20020404, 09:26 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Hour Format (2000)
Zeddythat cures it! One last Qany way to reformat the actual numbers themselves in column K so they conform to "Accounting" format with zero decimal places?
Thanks again,
Jeff

20020404, 13:43 #13
 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.
Legare Coleman

20020404, 13:46 #14
 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

20020404, 13:48 #15
 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"?