Time Conversion

I need help in converting time. I am using data from a time clock which
currently formats time as 1.5. When I input this data into a cell how can I
have it formated to read 1 hour and 30 minutes (1:30)

T.I.A.

Ed




0
elpedro (3)
7/10/2003 4:42:13 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
524 Views

Similar Articles

[PageSpeed] 53

What you could do in an adjacent row or column is format 
them as Time and then using a formula, divide the input 
time by 24(hours in a day).

>-----Original Message-----
>I need help in converting time. I am using data from a 
time clock which
>currently formats time as 1.5. When I input this data 
into a cell how can I
>have it formated to read 1 hour and 30 minutes (1:30)
>
>T.I.A.
>
>Ed
>
>
>
>
>.
>
0
caper (1)
7/10/2003 6:54:31 PM
>-----Original Message-----
>I need help in converting time. I am using data from a 
time clock which
>currently formats time as 1.5. When I input this data 
into a cell how can I
>have it formated to read 1 hour and 30 minutes (1:30)
>
>T.I.A.
>
>Ed
>
>I got this to work. First I had NOW() in a cell. TODAY() 
in a cell. I did cell 1 - cell 2. I then used custom 
format h:mm.ss. Hopes this helps.
>
>
>.
>
0
7/10/2003 6:56:29 PM
On Thu, 10 Jul 2003 09:42:13 -0700, "ELP" <elpedro@mindspring.com> wrote:

>I need help in converting time. I am using data from a time clock which
>currently formats time as 1.5. When I input this data into a cell how can I
>have it formated to read 1 hour and 30 minutes (1:30)
>
>T.I.A.
>
>Ed
>
>
>

Excel stores time as a fraction of a day. So 1:30 = 1.5/24.

If your time is in A1, in B1 enter the formula =A1/24.  Format
(Format/Cells/Number/Custom) [h]:mm


--ron
0
ronrosenfeld (3122)
7/10/2003 6:58:09 PM
Do you need to calculate with the 1:30 format, or can it 
be just text?  For just text, you can do this (A1 is the 
cell that has 1.5 value)

=INT(A1)   This will give you the hour part (cell1)
=60*(A1-(INT(A1)))   This will give you the minutes (cell2)
format those two cells as text then
concatenate(cell1,":",cell2)

Something like that?

0
danw8380 (1)
7/10/2003 7:16:19 PM
Reply:

Similar Artilces:

Display and calc of time formatted colum
Errr. I need a bit of help from someone please. I'm using Office XP and in Excel, what I want to do is create a column of time(s). The time is displayed as - mm:ss:xxx - so that e.g. 3:29:917 (means) 3 min, 29 secs, and .917 of a sec A. Is the format achievable as a display and if so, how? B. Can the numbers be aggregated (summed) Hi Bazza! Format mm:ss.000 But watch your data entry is 00:03:29.917 You can aggregate in the normal way =A1+B1+C1 However, if you don't want minutes to change to hours after you get to 60 use: [mm]:ss.000 Similarly if you don't want hours...

Weight conversion
Can anyone tell me if possible wether there is a way to get excel to understand stones and pounds (base unit 14) i.e. 10.12 needs to be 10.twelve not 10.onetwo Thanks If you want to convert to stones & decimals, then =INT(A1)+MOD(A1,1)*100/14, or if you want pounds, use =14*INT(A1)+100*MOD(A1,1) The above assumes that you'll enter 10st 7lb as 10.07, not as 10.7. If you're going to put in 10.7, then you'll need =14*LEFT(A1,FIND(".",A1)-1)+RIGHT(A1,LEN(A1)-FIND(".",A1)) for pounds, or =LEFT(A1,FIND(".",A1)-1)+RIGHT(A1,LEN(A1)-FIND(".&q...

Formatting Negative times
I'm trying to display the difference in the amount of time I exercise from month to month. Sometimes, it's negative. using a formula in EXCEL: =IF(AND(ISNUMBER(Q3),ISNUMBER(C3),C3>0,Q3>0),IF((T3-F3) >0,TEXT((T3-F3),"m:ss"),TEXT(-(T3-F3),"-"&"m:ss")),""), it works fine (the cells are in excel time, days). When I write a macro to do the same thing using: Cells(r, SumTAvgCol + DColD) = Format(-a, "-" & "mm:ss") I get -12:26. Always 12 as the minutes. If I use: Cells(r, SumTAvgCol + DColD) = Format(-a, &q...

outlook wants to install every time
OK, here is the deal: I installed office costumized, Run All From My Computer, and configured Outlook by pointing to the exchange server. Works fine. I restart the pc and logon with a different username and start Outlook. Now Outlook asks for the CD! Why? I previously said "Run all from my computer". As this pc will be used by many people who have no local administrator rights I do not want to spend time on giving them those rights and handing them the cd. Please tell me what I can do (during installation?) to prevent outlook from ever asking for the CD again, when a new user wants ...

Is there a way to delete multiple lines at the same time beside u.
Please let me know if there is other ways to delete multiple lines at the same time without using the Ctl key. Thank you. Hi Cherry are the lines adjacent to one another? if so just drag down the row numbers and then right mouse click & choose delete .. .if they're not adjacent and you don't want to use the Ctrl key to select them, how are you going to identify which rows you want to delete? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "cherry" <cherry@discussions.microsoft.com> wrote in message news:4BD...

How do you get Signatures to be in the email all the time?
Hi, I have a simple question. I have been using Windows Mail Desktop for awhile. I love it, except for two things. The one problem I have is when I am on the internet and there is a link to an email, if you click on it it brings up a window for sending an email. My signature does not show in that. How do I get it to show in that? I looked at all the options and I didn't see where I could do that. My sigature only shows when I click new messages, or replying. Not when I open up an email window from a link on the internet. How do I do that? Also, I asked this before but ...

Show more time in weekly view?
Outlook 2007 On default the weekly view is only showing until 4 PM without scrolling down. I know there is a way to let you see into the evening hours also without scrolling. Help please. Thanks... Bob ...

publisher conversion
How do I convert a .pub file into a .pdf file? I need that in order to perform mass mailings. Thank you very much derek_lmgc wrote: > How do I convert a .pub file into a .pdf file? > I need that in order to perform mass mailings. > > Thank you very much ============================ Which version of Publisher? There's an add-in for Publisher 2007: 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS http://tinyurl.com/v46jc And for earlier versions you could try the following freebie: PrimoPDF http://www.primopdf.com/ With your new program installed...open your proj...

Time clock on Headquarters
Hi, Does anyone know how we can check the timeclock (punch in / punch out) of the employees from the Headquarters Manager? Thank you! Rami Hi, if u have access on Microsoft customer source go to this link and u will find your required report https://mbs.microsoft.com/customersource/support/downloads/reportslibrary/rms/ModifiedReports/modifiedreports_rmsheadquarters.htm just copy it and drug it in hq folder reports. "Rami" wrote: > Hi, > > Does anyone know how we can check the timeclock (punch in / punch out) of > the employees from the Headquarters Manager? >...

Allow only one user to access workbook at a time
I need to set excel to allow only one user at a time to access a workbook. The workbook is NOT a shared workbook, but it resides on a network share. Multiple users need to access it, but only One at a time. I want all other users to get a message that the file is in use and they can not open it. Is this possible? I believe that is the standard behavior of a network file that is not shared; users who access the file when it is in use get a message the file is in use and whether they want to open as Read Only. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Emir_of_sh...

"Save As" dialog taking a long time to display
Most recently, in trying to save a Word document using the "Save as.." dialog, it took several minutes to display the dialog box. All the while, the SPOD was spinning away in Word. I could use other apps, even other Office apps (Entourage, Excel), but not Word. Is this a known problem (hopefully with a known solution)??? Mark Office 2004 11.2, OS X 10.4.3 P.S. I just noticed it's a problem with the "File, Open" dialog as well, in Word, Excel, and PowerPoint. But once I've used either one of the dialogs, there's no delay using either one of them afterwards du...

Same Worksheet Opens Every Time Workbook is opened
I created a two worksheet workbook. The first worksheet acts as a very simple welcome page - a graphic an a button. when you click the button, you are brought to the 2n worksheet which is filled with info. I would this 1st worksheet to open up as the first page every time. So, even if some edits the file, and saves changes. When it is opene up again, this welcome worksheet is the first page seen. Is this possible? P.S. (I do not know VB. I've been copying code from this for verbatumto use in my projects...which works out nicely thank you. -- Message posted from http://www.ExcelForu...

I know this quesion has been asked a million times but.....
the answers that I have found havne't worked. It's simple. I have query that has a date field. I have a form with two unbound tex boxes, one called txtstartdate and txtenddate. I also have other text boxes that work. One allows you to enter a location, and the other allows you to enter a customer. They are and/or. You don't need one to search by the other, but you can search by both if you want to. The code for the startdate and enddate txt boxes is: Between Forms!QBF_Form!txtStartDate and Forms!QBF_Form!txtEndDate It does not filter for a date. What am I doing wrong. Wha...

String to Number Conversion Problem
Hi, I'm running a german Office, which means Decimal-Seperator is "," and Thousands-Seperator is "." and I want to convert Strings which are using "." and "," as Decimal- and Thousands-Separator to Numbers. For example I want to convert "25.000" to 25. I've tried Application.UseSystemSeparators = False Application.DecimalSeparator = "." Application.ThousandsSeparator = "," vDouble = CDbl("25.000") Application.DecimalSeparator = "," Application.ThousandsSeparat...

How can I remove time from event banners?
Installed Outlook on new laptop and transferred over info. The banners on my all day events now include a time of 12:00am. I have tried everything to get rid of the time. How can I get the times off the banners? How did you transfer the info over? Usually you need to open each one and fix the time but if you used import, open the pst directly in outlook instead and see if they are ok. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subs...

Real Time Charting #2
I have 3 individual cells that automatically update every second: 1)Time; 2) Implied Price; 3) Actual Price. I track the changes of the Implied Price against the Actual Price along Time all throughout the day (2 line graphs plotted on 1 chart). During the day I copy and paste the 3 cells into a separate worksheet so they're stationary and in columns -- from that I create a graph. Is there a possible way/formula to have a predetermined range of cells auto populated every minute, predertmined range of individual cells set to populate at a specific time, or a graph that updates every min...

CString conversion
Hi, if my CString has got for example this value: <Greeting="Hello"> how Can I convert this to: &lt;Greeting=&quot;Hello&quot;&gt; Thank you for any help. Bop You can convert it to anything your heart desires! For a CString, you should be able to use the Replace() member function. Eg. CString bozo = "<Greeting=\"Hello\">"; // '\' to make valid expression bozo.Replace("<","&lt"); (etc.) balboos Robert Camsky wrote: > Hi, > > if my CString has got for example this va...

Appropriate number format to dispaly time in hours in XY chart
What is the appropriate number format to display time in hours:minute:seconds in a XY chart? [hh]:mm:ss -- David Biddulph "Saritha" <Saritha@discussions.microsoft.com> wrote in message news:8F67F716-CBD7-4B3F-8B29-8B88139B1A7B@microsoft.com... > What is the appropriate number format to display time in > hours:minute:seconds > in a XY chart? ...

Strong Private Key Protection: Need Password Prompt Every Time
Dear group, I'm using Outlook Express with it's S/MIME features for signing and encryption. For paranoid reasons, I need to make sure that every time my private key is used in Outlook Express, it prompts me with password. Currently, it only asks me the first time, then cached for the remainder of the session. Any way I can fix this? Thanks in advance.. --mendonan Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups...

Set Outlook 2007 Calendar Work Week End Time to the next day
We have users in Outlook 2007 (ver: SP2 12.0.6521.5000) that when they want to set their calendar work week end time to be the next day (after midnight) they get an error: "The end date you entered occurs before the start date". This are users that work the overnight shift, how can they specify their work week? Backend: Exchange 2007 "helm" <helm@discussions.microsoft.com> wrote in message news:7BEF5380-B9C3-4B87-93BE-814E4F0D47F1@microsoft.com... > We have users in Outlook 2007 (ver: SP2 12.0.6521.5000) that when they want > to set their calen...

Quicker Calculation Time
I have a sheet thath as multiple LOOKUP functions being used and couple custom functions being used. The calculations are extremely slow...is there a way to speed them up -- jeffcravene ----------------------------------------------------------------------- jeffcravener's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9 View this thread: http://www.excelforum.com/showthread.php?threadid=31977 Since you give no details, it's impossible to give you any specific advice. Check out: http://www.decisionmodels.com/optspeed.htm In article <jeffcravener....

Create a chart of totals over time?
I have a worksheet containing a User Type column and a Date/Time column. The User Type is a code between 0 and 9. The Date/Time is formatted as mm-dd-yyyy hh:mmAM/PM. Example: USER TYPE DATE/TIME 0 12-07-2009 11:09AM 0 12-07-2009 11:09AM 2 12-07-2009 11:09AM 1 12-07-2009 11:09AM 9 12-08-2009 1:19PM 2 12-08-2009 1:19PM 2 12-08-2009 1:19PM 0 12-09-2009 11:52AM 1 12-09-2009 11:52AM 9 12-09-2009 11:52AM As you can see, User Types can occur multiple times for each Date/Time value. I need to create a chart of the total number of type 0 and type 9 users over time. S...

Conversion of ALPHA
Can ALPHA FIVE be electronically converted to ACCESS 2000? ...

money 2005 crashes every time I try to open an account
This happens with only one account I have. Its a checking account with online banking at keybank. Other online features work fine. Whenever I try to open the account I get an error message the money must be restarted. I have tried using the salvage utility and it does not find anything wrong. I have also tried reinstalling money. I still get the same error. My guess is that there is something wrong with the "data" in that account. Has anyone else had a similar problem? Any ideas of what to try so that I can fix this. Its somewhat urgent in that I can't send any of my onli...

Appointment time increment
How can I change the appointment time increment from 30min to 15min? Assuming you are using 2003 (since you didn't include the version in your post), you can change this setting by going to the calender, right-clicking over the calender area, choosing "Other Settings" and then adjusting the time scale using the drop down menu. I find 15 minute increments to be a pain because the make it harder to see your day at a glance. In the future, it's best to include the version you are using in any posting here. ...