Time Format #6

I'm having a problem adding together journey times.

I have a start time (say 8:34:00) and an end time (say 10:23:00) and from 
this I calculate a journey time (1:49:00).  I then add this journey time 
(using the sum function) across a number of different journeys giving me a 
total.  This total is in some case in excess of 24 hours and Excel seems to 
revert to 0 at 24 hours and start the sum again.  So for example I can see 
when I highlight the range that the total is 27:49:00 but the sum function 
returns 03:49:00.

Any ideas?

Cheers

Lee 


0
someone913 (592)
5/14/2008 3:26:31 PM
excel 39879 articles. 2 followers. Follow

1 Replies
528 Views

Similar Articles

[PageSpeed] 1

Use a custom format:
[hh]:mm:ss

The []'s keep that rollover from happening.

Lee wrote:
> 
> I'm having a problem adding together journey times.
> 
> I have a start time (say 8:34:00) and an end time (say 10:23:00) and from
> this I calculate a journey time (1:49:00).  I then add this journey time
> (using the sum function) across a number of different journeys giving me a
> total.  This total is in some case in excess of 24 hours and Excel seems to
> revert to 0 at 24 hours and start the sum again.  So for example I can see
> when I highlight the range that the total is 27:49:00 but the sum function
> returns 03:49:00.
> 
> Any ideas?
> 
> Cheers
> 
> Lee

-- 

Dave Peterson
0
petersod (12005)
5/14/2008 3:46:18 PM
Reply:

Similar Artilces:

Auto Format Change
Re Excel 2002. I have a colleague in accounts. She has a spreadsheet of bank details. She formats her sort codes to text (an example is 20-40-70) and then saves as a CSV file. When she then opens the CSV file, some of the sort codes have changed to dates (example 20/05/2016). It doesn't affect all the sort codes so what can she do? I believe I have just solved it! I have advised her to not format to text, just leave as general and this seems to do the trick. "DM" wrote: > Re Excel 2002. > > I have a colleague in accounts. She has a spreadsheet of bank deta...

Conditional Formatting 05-06-10
Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 ...

error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 argumentswith[CT=char]
Hi, I am getting following error, error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 arguments with[CT=char] If you have the solution please provide it. Thanks, Shilpa Did you compile as Unicode, but still put in an ANSI format string like ..Format("%d") rather than .Format(_T("%d")) Tom "shilpa" <bharatid@cybage.com> wrote in message news:1174467883.296564.83070@p15g2000hsd.googlegroups.com... > Hi, > > I am getting following error, > > error C2661: 'CStdStr<CT>::Format' : no overloaded ...

Outlook 2007 Autocorrect with formatted text
I need to know how to enable Outlook to replace autocorrect entries with formatted text. In Word 2007, its as simple as clicking the radio button to enable it. In Outlook however, not so much. Here is what I am trying to accomplish -- this worked two weeks ago before my hard drive died, however, I can't remember how to recreate it. I enter my initials and autocorrect formats a signature with contact information. The path, Tools, Options, Spelling, Spelling, Spelling and Autocorrect Options, Autocorrect Options --- however, the "Formatted text" radio button is ...

Combining data (numeric format) in multiple cells into one cell (t
If you have data in multiple cells that is numeric format, can the data be combined into one cell as text? Example. Cell A1 is number of correct answers (9), Cell B1 is number of questions (10). In cell C1, would like to display data as (9-10)? Can a formula be created that will automatically do this so as to eliminate having to type the information? one way: =A1 & "-" & B1 In article <018B8827-4277-4C02-81E1-3BEDB61422A0@microsoft.com>, "GNAC SID" <GNAC SID@discussions.microsoft.com> wrote: > If you have data in multiple cells that is num...

Query to find records after specific date and time today
Our contract requires me to post a job for 10 calendar days and it must remain active until 8:00 am the date of the deadline. After that it must be removed from the list. If I post it today (3/15/10 regardless of the time) the job would have to be available right up through 3/25/10 at 7:59am - then it must be gone/unavailble at 8:00 am. The record has a field entitled "Deadline" where date and time would be entered into the field as "03/25/10 08:00:00 am" However, all records with that deadline for that particular date are removed at midnight (00:00:00) o...

Why does message show date/time in Sent box when draft (not sent)
Hi would be grateful if anybody can shed any light on the following problem. I sent an e-mail with a draft e-mail as an attachment. The draft e-mail has not been sent yet and is saved in Drafts in Outlook. Yet the recipient of the message says that the draft e-mail has got a date and time in the Sent box. Upon checking in Sent Items the message has not got a date and time in the Sent box but when you print the message off a date and time does appear in the Sent box does anybody know the reason for this? ...

Formatting changed when received by version 97
Hi, I have a problem with my Outlook XP. I am using Outlook XP with SP2 installed and using Word as my email editor. My problem is that when my emails are received by a 2000 or XP version of Outlook, all text is displayed correctly, but when a version 97 receives my mail then the font is changing mid sentence and extra blank lines are being added. The whole email look a complete mess. I know 97 only uses text and not HTML so something must be wrong with the way 97 removes the HTML tags. Has anyone had this problem or know of a fix? TIA Mark It is not so much that Outlook 97 does not ...

Time Remaining in Process
I have a lengthy process which takes upwards of an hour to complete.For simplicity, simply assume it is a basic Do While loop which takesapproximately an hour to complete. I have added a progress bar to myform to show the progress, but I was curious if there was any way tolist an estimated remaining time? I had tried to use something likethe code listed below but it doesn't give the expected results(probably because it's overly simple). Any help would be greatlyappreciated!dteTimeStart = TimeDo While CurrentCountInLoop <= MaxCountOfLoop dteTimeElapsed = Time - dteTimeStart * 24 ...

Formula for Date and Time duration
Morning All, I have to work out the time in which a case has taken to resolve on our system. Now in Excel I have the Case Date (Date opened), Case Time (Time opened), Closure Date, Closure Time. Does anyone have a formula that works out the Number of Days and Time it has taken to resolve a case? This needs to not include weekends and the working hours 08:30 to 18:30. This is way beyond my knowledge but I'm sure it can be done, I'm hoping someone has a formula for this already. -- Adam ----------- Windows 98 + Office Pro 97 Evening Adam! I have Date Opened in A1 Time Opened in ...

Conditional Formatting and selection
I have one column that has both name/address(in one row) and telephone numbers(in another row). I'd like to move the name/address info into it's own column. I've been able to do a conditional format to change the text info to a new color but I can't seem to mass select that info so I can move it all at once. Any ideas would be appreciated. if either the names or the telepphe numbers are all in even rows insert a helper column and enter =mod(row(),2) and copy down to the end of the data use autofilter on this column and select 0 select all and paste somewhere change the aut...

Format Function
Below is a snipplet of code that gets a recordset. I'm trying to output to a text file and keep my columns of data straight by using tabs. Because of the length of records in the field objRS!sShift, the vbTab code wasn't lining my column correctly, so I used the Format() function with the "@@@@@@" parameter and my data from the objRS!sShift now right-aligns. My new problem is I have a new field called "percentData" that is a percent that I need to display as 54.62% for example. Without the Format() function, the raw output of this field looks like 0.5462625 f...

Can't install 12.2.1 on any computers 10.4-10.6
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel "You cannot install Office 2008 12.2.1 Update on this volume. A version of the software required to install this update was not found on this volume." I get this error on every computer in the building, PowerPC computers running 10.4 and Intel computers running 10.4, 10.5 and 10.6. All the computers currently have 12.2.0 and have all current OS updates. Any ideas? On 10/1/09 7:54 AM, in article 59b7db0d.-1@webcrossing.caR9absDaxw, "macinmypocket@officeformac.com" <macinmypocket@officeformac.com...

Date Formatting #6
Please can somebody help me. I need to disable date formatting in Excel 2003 & 2007. Excel's "helpfullness" is being VERY unhepfull at the moment. It is changing my style codes to dates, and if you format the cell to text then it applies some useless number that means nothing to me instead of what is supposed to be in that cell. I need to open already created csv files - so formatting the cells beforehand isnt an option. Please can somebody tell me how to disable date formatting entirely in Excel. The application dumps data to csv files. Users manipulate this data and wo...

Protection from format changes allowing form to be filled in
I have a questionnaire matrix to be filled in by others. I would like to protect the form from changes in format. However, others should be able to fill in the form by choosing from drop down list and/or writing in cells. The way I have been trying is: tools, protection, protect sheet, insert password, ok. Tried variations with contents, objects, scenarios boxes all ticked/partically ticked/blank. However, in all cases, the sheet becomes read only and does not allow to be filled in. Thank you for your help. Unprotect the worksheet (if you have it protected). Select the cells that the users ...

Excel 2000: File format of Unicode text files
In Excel it is possible to save a worksheet as 'Unicode text'. Is there a description of the file format of these unicode text files? The problem is that there are some quotes (") added, if the cells content e.g. a line feed or quotes. So there may be some additional cases, where unexpected content is added by Excel and this would be very helpful to know. Thanks -- Rolf Keller I don't think MS has documented their file format publicly. Your choices may be to do the experimenting yourself to deduce their format, or to use a macro to create the Unicode text file. Here...

Format Cell Dialogue Box
Hi there, Why don't you make the Format Cells dialogue box modeless. So that you do not have to keep opening and closing it. Jimbola There are no "you"'s here, if your post is referring to Microsoft developers. This is a peer to peer News Group manned(personned?) by volunteers and not by Microsoft employees. Your "modeless" idea is a good one, however................ There are many shortcut keys for formatting cells without opening the dialog box. Perhaps one or more of them would suffice. See answer wizard on "shortcut keys" Gord Dibben Excel ...

Formatting problem #3
Hi, In thspreadsheet, I'm able to format only one cell but not other cell besides this cell. Could anyone tell me how I could fix this problem. Thanks. Rajan -- Message posted from http://www.ExcelForum.com Hi Rajani More imformation required. Can you select other cells?, the worksheet may be protected. Bob C. >-----Original Message----- >Hi, >In thspreadsheet, I'm able to format only one cell but not other cells >besides this cell. Could anyone tell me how I could fix this problem. >Thanks. >Rajani > > >--- >Message posted from http://www.ExcelFor...

An odd question about formatting
Ok, here's a toughie. I have a list of numbers in column A and I would like in column B a smiliar number of asterisks like this: 4 **** 1 * 9 ********* Any ideas? Amy Give the REPT Repeat function a try, I think it will fulfill your needs nicely. "cvgairport" wrote: > Ok, here's a toughie. I have a list of numbers in column A and I would like > in column B a smiliar number of asterisks like this: > > 4 **** > 1 * > 9 ********* > > Any ideas? > > Amy =REPT("*",A1) -- David Biddulph "cvgairp...

Format custom number: C-0000-L
Hi, I need to format a few cells in Excel to look like C-0000-L where "0000" represents four numbers and "L" represents a letter. I tried to do it in format - custom with C-0000-@ but it gives me an error message. I also tried to do it using the concatenate function but it doesn`t work. Can anyone please help me? Thanks! -- Sonia Custom format "C-"0000"-L" -- David Biddulph "Sonia" <Sonia@discussions.microsoft.com> wrote in message news:8EAB02FD-1AA5-4E80-880A-7C9A33A8FE01@microsoft.com... > Hi, I need to format...

re:Time Differences
This is a multi-part message in MIME format. ------=_NextPart_000_0021_01C4D2DA.06832CE0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0022_01C4D2DA.06832CE0" ------=_NextPart_001_0022_01C4D2DA.06832CE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a spread sheet where I am looking at what we call turnover times = and case length and I need to have difference in times sometimes be a = sometimesnegative and a positive but it won't allow a negative number to = display as hours and minutes any th...

Outlook shuts down #6
I am running Outlook 2000 (version 9.0.0.2711). Today it started shutting down for no reason while I have it minimized. I don't know if it will stay open maximized but I can't wait around to find out. It seems to run for about 3-5 minutes and then it just disappears. No warnings or error messages; it's just gone. It seems to start back up OK but won't stay running. Does anyone know what's going on? Is this a virus or something. I did a full system scan using Norton AntiVirus (freshly updated) and it found nothing. Any help would be appreciated. If you leave it un...

Data validation on a custom format
Hi again everyone.... I am currently using the following custom format: [h]:mm How can I use data validation to prevent invalid data from being entered into a cell of that format?? For my purposes, I only want data which consists of 1 or more digits in the "hours" field, folowed by one colon, followed by 1 to 2 digits in the minutes field. Do I need to enter some sort of formula into the data validation field?? thank you! There's an option in the Data|Validation|Settings tab that you can set for Time. It sounds like you ...

Format Lines
Would anyone be able to tell me why when using the drawing toolbar in excel I can not use things like 3d settings and Dash style. They are greyed out. Thanks Steve Never mind....I guess you have to put a normal line in first. Thank you. "Steve H." <steveh@mrinstitute.org> wrote in message news:emVotnOzEHA.3028@TK2MSFTNGP10.phx.gbl... > Would anyone be able to tell me why when using the drawing toolbar in excel > I can not use things like 3d settings and Dash style. They are greyed out. > > Thanks > > Steve > > ...

Conditional Formatting Based on Date
Allow me to establish the context of the situation. In column A I have a date that a form is received. In column B I want to condition the cells to display a color contingent on the amount of days that have transpired since the form was received. Column B actually documents whether a form has been approved or not. If it has not, then the appropriate color is displayed. If the form is approved, then the date of approval is put in the cell with no need for any color. The color is simply to act as an indicator of the amount of days that have passed without approval. For example, let's say ...