Converting Numbers to Time (A second probelm)

Due to several peoples help in this group I learned some very helpful
date/time functions but here is one that has since eluded me...

What I am trying to do it convert...

1/6/1900 22:40:25 to the total number of hours and seconds.

As if the start date was 1/1/1900 00:00:00.

Or in other words I have a
start date of
4/6/2005 11:54:53
End date of
4/7/2005 14:07:00
Total time of
1/1/1900 2:12:07

How do i convert the 1/1/1900 2:12:07 into 26:12:07

Thanks for all your help,
Steve
smonczka@hotmail.com

0
smonczka (53)
5/30/2005 9:23:09 PM
excel 39879 articles. 2 followers. Follow

4 Replies
421 Views

Similar Articles

[PageSpeed] 16

Just format the answer cell as [hh]:mm:ss

-- 
 HTH

Bob Phillips

"Smonczka" <smonczka@hotmail.com> wrote in message
news:1117488189.201342.94990@g47g2000cwa.googlegroups.com...
> Due to several peoples help in this group I learned some very helpful
> date/time functions but here is one that has since eluded me...
>
> What I am trying to do it convert...
>
> 1/6/1900 22:40:25 to the total number of hours and seconds.
>
> As if the start date was 1/1/1900 00:00:00.
>
> Or in other words I have a
> start date of
> 4/6/2005 11:54:53
> End date of
> 4/7/2005 14:07:00
> Total time of
> 1/1/1900 2:12:07
>
> How do i convert the 1/1/1900 2:12:07 into 26:12:07
>
> Thanks for all your help,
> Steve
> smonczka@hotmail.com
>


0
phillips1 (803)
5/30/2005 9:30:34 PM
Bob thank you so much, that worked.  But can you explain something to
me.  What is the difference between [hh]:mm:ss and [h]:mm:ss.  I had
originaly tried [h]:mm:ss and of course as you probably know it didn't
work.

Steve

0
smonczka (53)
5/30/2005 9:46:27 PM
Steve,

That is odd, [h]:mm:ss worked fine for me. The only difference is that with
one h, if you have less than 10 hours, say 8, it will show a 8, whereas with
two h's , it shows as hh. It is all formatting.

-- 
 HTH

Bob Phillips

"Smonczka" <smonczka@hotmail.com> wrote in message
news:1117489587.226115.306520@g43g2000cwa.googlegroups.com...
> Bob thank you so much, that worked.  But can you explain something to
> me.  What is the difference between [hh]:mm:ss and [h]:mm:ss.  I had
> originaly tried [h]:mm:ss and of course as you probably know it didn't
> work.
>
> Steve
>


0
phillips1 (803)
5/30/2005 9:51:00 PM
[h]:mm works fine, the only difference is if hours are in single digits then 
[hh] will display a leading zero

-- 
Regards,

Peo Sjoblom


"Smonczka" <smonczka@hotmail.com> wrote in message 
news:1117489587.226115.306520@g43g2000cwa.googlegroups.com...
> Bob thank you so much, that worked.  But can you explain something to
> me.  What is the difference between [hh]:mm:ss and [h]:mm:ss.  I had
> originaly tried [h]:mm:ss and of course as you probably know it didn't
> work.
>
> Steve
> 

0
terre081 (3244)
5/30/2005 9:53:06 PM
Reply:

Similar Artilces:

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: http://longre.free.fr/english/ It is downloaded in a zip file which also conta...

Excel
Is there a way to change an imported number such as: 7042626598 and display it as a phone number like: (704) 262-6598 ? Thank you, Be -- Message posted from http://www.ExcelForum.com Ben, Yes you can format the cells containing the 10 digit number to a phone number. Highlight all the required cell and on the Main menu choose Format/Cells/Number/Special/Phone Number Charlie O'Neill >-----Original Message----- >Is there a way to change an imported number such as: > >7042626598 and display it as a phone number like: > >(704) 262-6598 > >? > > >...

data labels from second column
Hi Column A is list of names (Bob, Sue, etc); column B is how much they collected (58, 12, etc); Column C is the date they did it on - so record 1 says Bob collected 58 on 10/07/07, for instance. I want to create a chart with the date for the x axis, amount collected as the y axis and data labels at each point within the graph giving the collector's name. So at X=12/07/07, y=58 I want it to say Bob within the chart area. Any help much appreciated. Regards Chandler On Mon, 3 Sep 2007, in microsoft.public.excel.charting, Chandler <Chandler@discussions.microsoft.com> said: &...

"Configuring Microsoft Office Basic 2007" every time I start
I added memory to my PC and Office 2003 started to misbehave (and it could not find a CAB file that was on the CD) so I removed it and upgraded to Office 2007. Now every time I start my PC it spends time configuring itself. I might add iTunes is the same. It means starting the PC is an even more laborious process than usual. Oh, and I have some annoying update to XML something or other that does not install either, and does not go away. It is ironic that the extra memory was to improve perforamnce. Every step forwards involves two steps back. Apart from swapping to App...

Planning Lead time-Item Vendors Maintenance window
Previously used manufacturing and MRP in v9 and the Planning Lead time on the Item Vendors Maintenance window (bottom left corner), we used to enter 7 for the number of days in 1 week. MRP then suggested to place PO's using these 7 days as one week. Now when we enter 35 for example ( V9 calculated 5 weeks for placing the PO) Now since V10 it looks like it is calculating 7 weeks lead time (35 days divided by 5 days) Did something change with V10 on this field and how MRP uses it with the Request Resolution window? thanks! -- Doug The system now looks at calendars and can conside...

UTC time to Central Time (US)
Hello. I need to convert UTC time to US Central Time Zone. The date is in one column and the time is in another below. A | B ---------------------------------- Date | Time ---------------------------------- 2005-10-24 | 12:15:00 I want to convert the Date and Time columns in UTC to a single Date and Time column in US Central Time. Thanks for any advice Assuming UTC is the same as GMT then the difference is 6 hours STD, so if the time is 06:00 AM it is 12:00 PM GMT, thus add 6 hours and since the y are all numbers just add =A1+B1+(6...

Item level revision numbers on POs
The Item Engineering info on inventory items in manufacturing includes revision numbers for items, however because that info is in the manufacturing dictionary we can't include through report writer on POs. I'd like to develop a routine that would copy the revision number from the item engineering table to one of the user-defined tables in inventory. Any suggestions on how to do that? Or an alternate approach to the problem? -- Jim@TurboChef Jim- This script will put the current Rev level from Item Engineering into User Category 1 on the item master. update iv00101 set usca...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

Outlook hangs ,7th time :-(
Hi all, In our company we are using Exchange 2000 as mail server, and outlook 2002 sp2 as email client. Each client is configured to download its emails to a local pst file. We are facing the following issue : If you add a rule to move messages from inbox to a particular folder, during send/receive and just after receiving 3-5 messages (and these messages are moved to their folders) the outlook stops responding. If you run outlook with /safe parameter it will not hang! If any one could advise I would be gratefull. Thanks, Osama. ...

Conver Numbers to Log 10
How do I convert column of numbers (n=3000) to log 10 column of numbers? Assuming that your column is column A, one way: In an unused column, enter =LOG(A1) and copy down 3000 cells. Copy the column, choose column A and choose Edit/Paste Special, selecting the Values radio button. In article <0CDE6301-FC9C-488C-9D9F-3AD26B46EFCE@microsoft.com>, "Fishfan" <Fishfan@discussions.microsoft.com> wrote: > How do I convert column of numbers (n=3000) to log 10 column of numbers? ...

error numbers
Does anyone know if microsoft has a way to look up errors and their resolution by ERROR NUMBER? I have never found anything like that. Now they have something called "Events and Errors Message Center" but even that doesn't appear to let one look up errors by error number! Thanks Hi, Fredrated schrieb: > Does anyone know if microsoft has a way to look up errors and their > resolution by ERROR NUMBER? > > I have never found anything like that. Now they have something called > "Events and Errors Message Center" but even that doesn't appear to...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

What is the maximum number of users that can share Excel File?
According to the Excel Help file, all users on your network, unless you restrict the number. "pfine" wrote: > ...

converting dates
Hi All I have a couple of excel problems to do with dates (Excel XP/2002). I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365. Secondly does anyone know a formula that sorts out leap years (see above). Any help, suggestions or tutorial links greatly appreciated Rexmann As long as you assume 1 year is always 365 days =INT(A1/365)&" year(s) "&MOD(A1,365)&&q...

Adding time
I want to add time,like a sum. 12:00+12:00+12:00=36:00? I keep getting a basic time like 12:00. Could you please supply a formula. Thank you Hi In the cell with the formula, Format>Cells>Number>Custom [h]:mm Putting the [ ] brackets around h allows it to sum past 24 hours. Regards Roger Govier trucker wrote: > I want to add time,like a sum. > > 12:00+12:00+12:00=36:00? > > I keep getting a basic time like 12:00. Could you please supply a formula. > > Thank you > ...

lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...

Is there any way to convert a pdf document into an excel document.
Is there any way to convert a pdf file into an excel document? ...

Maximum number of bits
I have a requirement to find the maximum number of consecutive zeros in a row in a 32 bit binary number. For example 1111 1111 1111 1000 1111 1111 0000 1111 = 4 1111 1110 1111 1110 1111 1111 1110 1111 = 1 I am actually storing the number in a BIGINT for simplicity. I can do this in a udf with a look fairly easily but was wondering if there is something more sql that will do the job. Cheers, Michael That's an interesting question. Does it include leading zeroes? 0000 0000 0111 1000 1111 1111 0000 1111 = 9? -- Thanks Michael Coles SQL Server MVP ...

Time Entry
A simple problem I would appreciate your help with. I have a work sheet with several cells where time is to be entered in the format hh:mm. Unfortunately a lot of users are enetering it in the format hh.mm. Is there any way I can automatically convert to the right format, or not accept the (.) but only (:). Thanks for your help. Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then MsgBox "invalid entry" End If End Sub Enter the above code in the sheets module Mangesh -- mangesh_yadav -------------------...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

Run-time error 438?
Hello, I am trying to create an edit control for my form. I lock my textboxes in properties and then I created a command button with an event on onclick. My codes are working for most of them expect a few. When I click the button it gives me Run-time error 438. My code is based liked this. Me![Control].Locked=False Private Sub Edit_Record_Click() Me![Drago ID].Locked = False Me![Drago Claim Number].Locked = False Me![Dealer Claim Number].Locked = False Me![Dealer].Locked = False Me![Store].Locked = False Me![Machine Acres].Locked = False Me![Part Fail Date].Locked = False - NOT WORKING ...

finding common numbers in large lists
Hello, I have two large lists of numbers (on two seperate worksheets). By large, I mean that one list is 1000+ numbers, and the second list is 3000+ numbers (each number is in its own cell). The numbers are of the form: xxxx.xxx (that is, they are precise up to three places after the decimal) 1) Is there some quick way that I can find the numbers that are common to both lists? 2) Is there a way I can find the numbers in common to both lists using only the whole number portion (diregarding what comes after the decimal of each number)? Thanks! Hi see: http://www.cpearson.com/excel/duplic...

Changing multiple appointment times
I have hundreds of appointments and would like to move them back one hour. I know there is a link in microsofts support site that lets you change the time zone without changing appointment times. However, recurring appointments are not supported by that method. Does anyone know of a way to move all my appointments back one hour? ...

time sheet template available
I am looking for a bi-weekly time sheet template (or weekly). Having problems with excel correctly computing total times (works great for one day). bsydnes, heres one, http://www.cpearson.com/excel/overtime.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "bsydnes" <bsydnes@discussions.microsoft.com> wrote in message news:CA5250B0-8F92-41BB-BB5C-04F085C009F2@microsoft.com... > I am looking for a bi-weekly time...

creating a multilevel numbering sysem
I'm trying to create a multileve numbering system ex: 100, 100.1, 100.1.1, 100.2, 200, 200.1, 2001.1, 200.2, and so on. I would like to have at least 3-4 tiers under each number that would automatically number. And you want this in Publisher? I don't think so... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "v" <v@discussions.microsoft.com> wrote in message news:C1F59342-9F2F-4E91-BE27-0BA992EB17BE@microsoft.com... > I'm trying to create a multileve numbering system > ex: 100, 100.1, 100.1.1, ...