 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007Time - 0.25 - 15 mins
Hey Is there a formula that can convert hours in decimal into real time? I need to convert say 2.25 hours into 2 Hours 15 Mins... Any ideas?  SignatureMark Divide by 24 and format the result as hh:mm =a1/24 (and format nicely) > Hey > [quoted text clipped - 6 lines] > -- > Mark SignatureDave Peterson Ps. If you want to update in place, you can: put 24 in an empty cell edit|copy that cell select the range to fix edit|paste special|check divide Format that selected range the way you like clear the cell with 24 in it. > Hey > [quoted text clipped - 6 lines] > -- > Mark SignatureDave Peterson In an empty cellenter 24m then copy it. Select the cell(s) that you want to convert, right-click and select Paste Special > Divide then re-format the cell as time. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hey > [quoted text clipped - 3 lines] > > Any ideas? As Dave Peterson had suggested: "If you want to update in place, you can: put 24 in an empty cell edit|copy that cell select the range to fix edit|paste special|check divide Format that selected range the way you like clear the cell with 24 in it." And, if you actually want to spell out "Hours" and "Mins" as indicated in your post, you could custom format: [h]" Hours "mm" Mins" --alan > Hey > [quoted text clipped - 3 lines] > > Any ideas? Hi Mark Here is my proposal : with your value in A1 =TEXT(A1/24;"hh \Hour\s mm \Mi\n\s") Greetings from France Dav > Hey > [quoted text clipped - 3 lines] > > Any ideas? I see three problems with your formula, one of which is easily fixed: 1. It doesn't work using a semi-colon. Change the semi-colon to a comma (i.e. =TEXT(A1/24,"hh \Hour\s mm \Mi\n\s") , and it will work. (Perhaps there's a difference between American and French versions?) 2. If you've got time entries greater than 23.99, it's not going to work. (24.00 becomes 00 Hours 00 Mins, and 25.75 would be expressed as 01 Hours 45 Mins. 3. Having in effect changed the entries to Text, you've lost the ability to calculate. --alan > Hi Mark > [quoted text clipped - 14 lines] >> >> Any ideas? Hi Alan 1. Well yes in French it works with semi-colon and not comma. I'll know it for the next time. 2. 3. I agree with you with the two other points. It depends also what does he want to do with it. It seemed to me a simple and easy solution. Greetings from France Dav >I see three problems with your formula, one of which is easily fixed: > [quoted text clipped - 29 lines] >>> >>> Any ideas? You're right. If he has entries of 23.99 and under, and doesn't need to calculate, it's a great solution. I very much like the idea of formatting being part of the formula. --alan > Hi Alan > [quoted text clipped - 44 lines] >>>> >>>> Any ideas?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.