1. ## 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.
Jeff

Like this??

Jeff

4. ## 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

5. ## 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. ## 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>

7. ## 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

8. ## Re: Hour Format (2000)

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

9. ## Re: Hour Format (2000)

Thanks, Legare; works great!

10. ## 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. ## 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. ## 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. ## Re: Hour Format (2000)

Yes they should. I fixed the formulas in J and K to round like they should.

14. ## 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.

15. ## 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 Last

#### Posting Permissions

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