day and time convertion

boss now wants me to convert dd:hh:mm:ss into total time eg

02:02:30:59 comes out as 50 hours 30 minutes 59 seconds 

thanx in advance


---
Message posted from http://www.ExcelForum.com/

0
2/1/2004 1:17:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
388 Views

Similar Articles

[PageSpeed] 16

one way:

   Format/Cells/Number/Custom  [hh]:mm:ss

In article <chris.howes.10y8mb@excelforum-nospam.com>,
 chris.howes <<chris.howes.10y8mb@excelforum-nospam.com>> wrote:

> boss now wants me to convert dd:hh:mm:ss into total time eg
> 
> 02:02:30:59 comes out as 50 hours 30 minutes 59 seconds 
> 
> thanx in advance
>
0
jemcgimpsey1 (104)
2/1/2004 1:56:47 PM
chris.howes wrote:
> *boss now wants me to convert dd:hh:mm:ss into total time eg
> 
> 02:02:30:59 comes out as 50 hours 30 minutes 59 seconds 
> 
> thanx in advance * 

have tried this and it will not reformat the cell just leaves th
original data there 02:02:30:59 will not convert i

--
Message posted from http://www.ExcelForum.com

0
2/1/2004 2:10:50 PM
On Sun, 1 Feb 2004 07:17:01 -0600, chris.howes
<<chris.howes.10y8mb@excelforum-nospam.com>> wrote in
microsoft.public.excel.misc:

>boss now wants me to convert dd:hh:mm:ss into total time eg
>
>02:02:30:59 comes out as 50 hours 30 minutes 59 seconds 

You posted this question already about 3 hours ago. Doesn't Ron
Rosenfeld's answer, which he posted 2 hours later, work for you?

Just in case: his answer was:
  =LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))
and format as [h]:mm

Now you also specify to have seconds in the output; format as [h]:mm:ss

If you literally want "50 hours 30 minutes 59 seconds", feel free to ask
again.

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
Michael
2/1/2004 2:26:34 PM
On Sun, 01 Feb 2004 06:56:47 -0700, JE McGimpsey
<jemcgimpsey@mcgimpsey.com> wrote in microsoft.public.excel.misc:

>one way:
>
>   Format/Cells/Number/Custom  [hh]:mm:ss

That doesn't work here (Excel 11) for the data the OP describes.

>In article <chris.howes.10y8mb@excelforum-nospam.com>,
> chris.howes <<chris.howes.10y8mb@excelforum-nospam.com>> wrote:
>
>> boss now wants me to convert dd:hh:mm:ss into total time eg
>> 
>> 02:02:30:59 comes out as 50 hours 30 minutes 59 seconds 

-- 
Michael Bednarek   http://mbednarek.com/   "POST NO BILLS"
0
Michael
2/1/2004 2:35:45 PM
In article <chris.howes.10yb40@excelforum-nospam.com>,
 chris.howes <<chris.howes.10yb40@excelforum-nospam.com>> wrote:

> have tried this and it will not reformat the cell just leaves the
> original data there 02:02:30:59 will not convert it

If you have a string "02:02:30:59" then reformatting will not work, 
since the value is not a number.

Use Ron's answer:

   =LEFT(A1,FIND(":",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))

in an empty column, and copy down.

Then copy the converted values, select the original values and choose 
Edit/Paste Special, selecting the Values radio button. Format with 

   Format/Cells/Number/Custom    [h]:mm:ss.

If you instead want  50 hours 30 minutes 59 seconds, format as

    Format/Cells/Number/Custom    [hh] "hours" mm "minutes" ss "seconds"
0
jemcgimpsey1 (104)
2/1/2004 3:56:07 PM
Reply:

Similar Artilces:

Auto Insert the present time when a cell is filled with a data,
"Insert Time when a cell is filled with data, but don't update the time how to use the following code and where to put this code can someone please explain a step by step guide for this please? Once the time is inserted, you could edit/copy/paste special/values. You might also try setting up a macro that would do the now function and then paste values. The following code will auto insert the time using ctrl+y Sub Time_Constant() ' ' Time_Constant Macro ' ' Keyboard Shortcut: Ctrl+y ' ActiveCell.FormulaR1C1 = "=NOW()" Range("A1").Selec...

converting scanned items
I'm scanning things into the computer and they're saving in the format of tagged imaged file format and I want to open it in publisher to edit it but I cannot. You cannot edit bitmaps in Publisher, just to crop and a few other rudimentary chores. Use a paint program to edit the scan, even MS Paint will do. Tif images are not created equal. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Mary" <custserv@capital.net> wrote in message news:10ca01c351ff$d4b13740$a001280a@phx.gbl... > I'm scanning things into the computer and they're s...

Converting publisher files to PDF with Adobe Acrobat
Hi there, Just designed a CD insert for my band in MS Publisher that is 360mm x 120mm, which with three folds will fit into the front of a standard CD jewel case. I want to save it as a PDF file so that I can e-mail it to the band and the printers for approval before they go with the final print run. Unfortunately, everytime I create the PDF file, Acrobat Distiller crops the document and saves it over two pages (as it is wider than the typical landscape A4 document). Can anybody help me to save it on one page as one complete PDF document - as it appears in Publisher - with the ability to sc...

Outlook Time Problem
Hello all I am having a problem with daylight savings time on several of my machines. They are running Outlook 2000 and Windows XP. Since the change to DST these users' calendar's are off by one hour. I have checked to make sure the "adjust for DST" setting is set. When the DST option is unchecked the appt's show up at the correct times. Do you have any suggestions of what to look for next? Thanks John Did you check time zones on the user's pc? And also XP has this time server thing i disabled it. Since for laptop when the saleman travels the time doesn't...

Snapshot isolation level to a point in time
In SQL Server 2005 Microsoft implemented the snapshot isolation level. SET TRANSACTION ISOLATION LEVEL SNAPSHOT This is great, but what if you also could specify a time or an lsn number. SET TRANSACTION ISOLATION LEVEL SNAPSHOT TO TIME = '2009-12-09 12:01:01' For this to work you would also need a database option to specify how long SQL Server should keep the information in the version store. It would have a considerable performance and storage cost, but I think that it would be a really useful feature. You could for example do a select to any point in time for th...

Looping Macro
Dear All. I have managed to piece together a complex code to perform a series of actions for me. The macro allows the user to select the folder containing the most up to date data, it then open each of the text files in that folder and converts them to excel files. Then I am trying to get it to copy and paste the data in each of those files onto the relevant sheet of the master workbook. I am trying to do this by matching the beginning of the file name and the beginning of the sheet name (so the macro knows where to put each files information). I am getting a run time ...

Run-time error 424 Object required
Anyone know what causes this error and how to fix it. Whenever the login screen is required to come up either after a transaction or when hitting rf9 to secure the error pops up and RMS crashes. I am running RMS 2.0 w/ SP2. Oddly enough I had the issue on a few of the machines and reindexed the database as I recently upgraded from 1.3 and some of the terminals were fixed while some new ones started having the issue. -- NateS ...

Timing in PowerPoint Viewer
I just spent a long time creating a slideshow presentation in PP 2007, to be put on CD and distributed. I included narration. I put the presentation on CD, and when I open it up in PP viewer, the timing is way off. Pictures, etc. open up several seconds before the narration reaches that point. It works fine when I run it on PowerPoint, but the viewer throws it off somehow. Any suggestions on what I'm doing wrong? http://www.soniacoleman.com/Tutorials/PowerPoint/synchronizing.htm -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.ech...

Counting hours mins secs between 2 dates and times
Hello In my worksheet I have 4 columns. I have a start dates and end dates as well as times for start and end. I need to calculate the time elapsed between the start date and time till the end date and time. But it must display it as HH, MM, SS. I must show whether it has gone over 100 hours. That is the urgent level of my time frame I need it to look something like this Start Date Start Time End Date End Time Elapsed Time 06/01/2004 7:00 AM 6/08/2004 7:00 PM 144:55:24 I keep coming up with 00's Thanks Bob Use =(C2+D2)-(A2+B2) , but you'll need ...

adding race times in minute'second"millisecond format
i'm stuck. i've been trying to add track times together to get the total time spent on each cup. not much else to say, so i'll show you what i have. http://snorland.com/store/tedor/fzeroex.gif it should add up to... what, 6'20"302? can't get excel to recognize it. thanks in advance. -tedor ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ tedor, I entered the times you gave as follows, using format "[m]:ss.000"- 00:42.595 ...

Date, Time location Stamp
Sometimes when proposing a meeting the Date time location get put in the message box. How do I initiate this to happen for all meetings? see below example When: Wednesday, February 24, 2010 9:00 AM-10:00 AM (GMT-05:00) Eastern Time (US & Canada). Where: Lupine - 8 - SR *~*~*~*~*~*~*~*~*~* -- CharleneN Is this an Access database question or an Outlook question? This is an Access forum. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Charlene N" wrote: > Sometimes when proposing a meeti...

using excel for the first time,want a document frowhere can learn
using excel for the first time,want to know the basic about excel from the start,if a document is provided that would be more usefull Regards pooja thats an awfully broad question....Excel can be used in many many many many ways..........give us an idea of what you want to do.....there are many templates available too -- paul remove nospam for email addy! "pooja thimmaiah" wrote: > using excel for the first time,want to know the basic about excel from the > start,if a document is provided that would be more usefull > > Regards > pooja pooja thimmaiah wro...

Automatically updating insertion of first and last day of year
I use a drop-down list for choosing between a calendar and fiscal year; if I select "Calendar" in cell A1, what would be a formula for inserting the first day of the year (in cell B1) and last day in the year (in cell B2) that would update each year without having to manually enter the dates 1/1/09 and 12/31/09 in the formula, as shown below: The formula I have now is: (For Cell B1) =IF(A1="Calendar","1/1/2009") -- MZ =IF(A1="Calendar","01/01/" & YEAR(TODAY()),"Value if not Calendar") "MZ" wrote: ...

How to calculate date and time
please help me in something i have two cells: 31/10/2004 18:00 01/09/2004 07:00 i want to subtract the two cells the result i am taking is 29/02/1900 11:00 but the correct result is 29 and one month what can i do thanks http://www.cpearson.com/excel/datedif.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "kasey" <kasey@discussions.microsoft.com> wrote in message news:1A258485-6D28-4AD0-9A52-C282609BAA83@microsoft.com... > please help me in something > i have two cells: > 31/10/2004 18:00 > 01/09/2004 07:00 > i want to subtract the two cells &...

How do I convert an Access form into a data access page?
Is there a way to do this instead of redesigning a data access page? "Chris" <Chris@discussions.microsoft.com> wrote > Is there a way to do this instead of redesigning a data access page? It may not be such a great move to do so... Data Access Pages have always been limited, weren't well accepted, and have been "deprecated" in Access 2007 -- existing ones are still supported, but you can't modify them or create new ones with Access 2007. They are "on the way out." Larry Linson Microsoft Access MVP ...

Auto insert time in pocket MS Excel
Hello, I'm trying to set up my time accounting for projects in an Ipaq pocket pc. Using the onscreen keyboard, I can autoinsert the date using ctrl+:, the same as with the external keyboard. Using the external keyboard, I can autoinset the time using ctrl+shift+;. This doesn't work using the onscreen keyboard. One command is for date, another for time. If you can help me with this, even if to say it just can't be done, it would be very helpful. Thank you, Elaine ...

adding times #3
I have a timesheet and I need a formula that will ADD the following times: 1.67 1.11 The natural result is: 2.78 But I need : 3.18 (Need the minutes to convert over. 60 minutes = 1 hour) I dont to have to manually add everthing over .60 Any ideas? Thanx J.W., you are giving yourself a lot of trouble by trying to work with decimal times. It is much better to use XL times like 1:40 1:07 (note that the hours and minutes are separated by a full colon) You can then simply add the times like A1+A2 or SUM(A1:A2) If your total is liable to exceed 24 hours then custom format the cel...

Sent Email Always Recieved in Times New Roman Font
I would like the body of all email I send out to use the Arial 10pt font. I've set my message format to HTML. Via Tools > Options > Mail Format > Fonts, I've set my Message fonts to 10 pt. Arial. When I compose a message, it uses the Arial font. If I send the message to another individual, they receive it, and the body of the email is using the Times New Roman font. In my sent items, the message is still using Arial. I've sent the message to multiple people, and always, when they see it in their inbox, the Times New Roman font is used. These are all people using...

Excel date format convert to string format
I have this problem I would like to change a column format from (m.d.yyyy <== date format) to (m.d.yyyy <== string format) Any method I can use ? thanks in advance Enter this macro: Sub gsnu() Dim s As String For Each r In Selection s = r.Value r.Clear r.NumberFormat = "@" r.Value = s Next End Sub Then select the cells to be converted and run the macro. -- Gary's Student "man" wrote: > I have this problem > I would like to change a column format from (m.d.yyyy <== date format) to > (m.d.yyyy <== string format) > > A...

Cannot add a time for an event
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Hi, <br><br>I cannot add a time for an event in Calendar. When I try, only the AM or PM is available. The box is the correct size when in calendar, but when I go to preferences, there is no room in the box for the time. There is only room enough for the AM or PM status. I fixed this by going into system preferences, date &amp; time, open language and text, and clicking on the formats button. There, I changed the region to United States (It was custom). This solved my iss...

Valentine's Day is coming ! How to thanks for your beloved ?
Take the chance of the Valentine=92s Day to show your love, appreciation, and humor for your love, family, and friends: =93Clone=94 your lover to surprise your sweet heart! =93Clone=94 your boss to please your supervisor! =93Clone=94 your friend to amaze your pal! You also can: Sign up to win up to $500! Place order to win up to $1000! Just Click Below: ArtsToAll.com http://www.artstoall.com/tranAdvURl.action?tag=3D2&sendURL=3Di4sCtfssjsss Thank you for your interest! ...

inserting times into cells to make a chart
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am inserting data into excel to make a chart using time. i have selected the formatting palette and highlighted all the relevant cells and selected &quot;time&quot;. The cells now only record 0.00 no matter what value I put into them. I am trying to get the cell to reflect values like 3 hours and 20 minutes as 3.20, but it just goes back to 0.00 <br> any help will be appreciated thanks Hi Traci: A cell formatted as a "Time" does NOT store "hours and minutes" � it stores a ti...

Logic formula using Time
I am trying to write a formula that compares a variable time to a fixed time. If the variable is greater than the fixed, I assign it the value "1" and if it is less than, I assign in the value "0". The formula I'm trying to use is: =IF(A1>A2,1,0) where A1 is the variable time. The problem I have is that the fixed time is 12:00 AM, so Excel is reading all variable times as GREATER than and assigning a "1". How can I write the formula so that 11:50 PM (the previous calendar day) will read as LESS THAN the fixed 12:00 AM (the following calendar da...

opening 2 excel workbooks at the same time
Hello, I have Microsoft Excell 2000 on a windows 98 platform. I have a dual video card which enables me to view two different views on two different monitors at the same time. How can I open and work on two different excell workbooks at the same time? I can accomplish this in microsoft word by holding the shift key. I have tried this method without any good results. Any suggestions would be welcomed. Thank you. -- candulj candulj, There are a couple of ways: Extend the Excel application window across both monitors. It may not maximize across both monitors, in which case you can lea...

Date and Time Picker
I am trying to use the Microsoft Date and Time Picker Control, Version 6 so people can choose a date by the calendar and when I do one for the page it seems to work ok but when I put two , then they do not retain their place on the page the next time I open the file. I go into Design Mode then insert and choose the bottom right for more options to find the Microsoft Date... I can zoom in / out and it seem to correct itself , but looks odd when I first reopen. I have tried saving the file as .xlsm and xlsx ? Any suggestions? maybe code for the sheet? ...