time formulas

Can anybody give me an advice

Lets say i put in first column dime of departure and in second one time of
arrival (of course i know have to insert the time ). In third column i get
the time spent somwhere. So far everything is ok. The problem occ urs when i
want the sum of  all differences in the third column (rows are months
dates). The autosumm formula wont work and the result is completely wrong.


thanks
miro


0
7/28/2004 6:55:15 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
678 Views

Similar Articles

[PageSpeed] 58

Hi
format the resulting cell with the custome format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany


axiom wrote:
> Can anybody give me an advice
>
> Lets say i put in first column dime of departure and in second one
> time of arrival (of course i know have to insert the time ). In third
> column i get the time spent somwhere. So far everything is ok. The
> problem occ urs when i want the sum of  all differences in the third
> column (rows are months dates). The autosumm formula wont work and
> the result is completely wrong.
>
>
> thanks
> miro

0
frank.kabel (11126)
7/28/2004 6:59:35 PM
Hi Miro!

Just to add to Frank's reply.

The sum is not "completely" wrong but is right! It is only formatted 
in a way that is not very friendly.

Excel treats times as the integer part of the date / time serial 
number system whereby dates are the number of days since 31-Dec-1899. 
The time in any particular day is a decimal part of one day.

Your time difference of (say) 6:00 is recorded by Excel as 0.25 and if 
you format as time hh:mm you get to see this number in the form 6:00. 
However, if you format dd-mmm-yyyy hh:mm you will see 1-Jan-1900 06:00

When you add times and exceed 24 hours the serial number goes above 
one and a "simple" time format will only display the integer part of 
the result.

So if you add 09:00 and 21:00 the result in format hh:mm will show 
06:00.

But the result in full format dd-mmm-yyyy hh:mm will be 2-Jan-1900 
06:00

Formatted as general or numeric will show 1.25

So the result you are seeing is right. And as Frank has said so 
succinctly, you can see the time in hh:mm only by formatting [hh]:mm

These principles of time formatting in Excel are important when it 
come to (eg) multiplying by hourly rates. 06:00 multiplied by $10 is 
$2.50 because 06:00 is 0.25 and 0.25 * 10 is $2.50. You have to use 
YourTimeLapse * 24 * 10.

If I'm teaching my Grandmother to suck eggs, Sorry Gran!

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au 


0
njharker (1646)
7/29/2004 11:44:30 PM
Reply:

Similar Artilces:

Vb code working one time
Hi, I have a form for adding data to a table. In this form , there is a command button for adding those data and it has the following code: Private Sub Command22_Click() On Error GoTo Err_Command22_Click If IsNull(Me.Combo28) = True Then MsgBox "You Must Select a S.O.Processor First.", vbCritical, "No Processor Selected..." Me.Combo28.SetFocus ElseIf IsNull(Me.CONTRACT_NO) = True Then MsgBox "You Must Input a Contract No. First.", vbCritical, "No Contract No. Input..." Me.CONTRACT_NO.SetFocus ElseIf IsNull(Me.CONTRACTOR) = True Then ...

Protecting my formulas
Is there a way that I can protect cells where i have my formulas so other users CAN'T SEE THEM in the formula bar or in any other way? Thanks, Marko Hi Marco, 1. Select the whole sheet 2. Go to menu Format>Cells... tab Protection, uncheck Locked, press OK 3. Select cells/ranges to protect 4. Go to menu Format>Cells... tab Protection, check Locked and Hidden, press OK 5. Go to menu Tools>Protect Sheet... and follow the instructions... Regards, KL "marko" <marko19@gmail.com> wrote in message news:1134986224.434038.101220@g44g2000cwa.googlegroups.com... > I...

Format all cells containing formulas
I'd like all cells which contain calculated values to show up in different color (or some other formatting change). I know I can toggl formula fields to be visible and manually update each one individually but I'd ideally like something that automatically formats new formul cells (and even applies to new .XLSs and worksheets). Seems like a obvious thing to want to do, but haven't found this anywhere in th UI. A usable fallback option would be a macro to search the worksheet an apply (eg) Red Bold to every cell containing a formula. Any pointers? Thanks, - -- Message posted fr...

Formula Bar Missing
My formula bar has disappeared! I can't retrieve it with the = view>formula=20 bar command. I tried trashing the tool bar file and it didn't do = anything. =20 Any other suggestions would be most helpful. Mahalo Try Options from the Tools menu, click on the View tab, and check Formula Bar in the top row. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Angie wrote: > My formula bar has disappeared! I can't retrieve it with the view>formula > bar command. I tried trashing t...

Replacing Formula with a Value
Is there a way in native EXCEL to replace a formula with a value, so a value is then stored in the cell. I have a formula which checks if the date in each row is today's date, using the TODAY() function. I it evaluates true (today's date), then I read the values from a row of cells near the top of the sheet. I want to be able to replace the formula with a value if the formual evaluates true. Thanks for your help. Copy the cell(s) in question, then choose Edit | Paste Special... and choose "values" in the dialog. Click OK, and the formula will be replaced. -- HTH - ...

conditional formatting with FORMULA... Please HELP! #2
Hi all, I have price data in rows like this (each price takes up a cell): 5 5.43 6 6.25 6.50 8 15 9.25 9 8.75 8.50 8.50 8.50 8.50 13 I need to show trends via conditional formatting where on 3 or more consecutive days prices fluctuate EQUALLY by 0.25 or more each time, up or down... IE, with the above data, I would want to "flag" let's say, in red fill color, the sequence 6, 6.25, 6.50 as well as the group 9.25, 9, 8.75, 8.50, but NOT the group of 8.50, 8.50, 8.50. I have tried if/and/or statements again and again and come close on simpler tre...

Adding Time Format
I am adding time for a day and would like the total to be returned in a different format. If I needed the time between 8:30am and 12:00pm added up it displays 3:30 as my total. How do I have it display 3.5 as the total? Currently the total cell is formatted [hh]:mm. Thanks for the help. you need to do something like this A1: 8:30 AM A2: 12:00 PM A3: =(A2-A1)*24 and then format A3 as a number. "jtinne" wrote: > I am adding time for a day and would like the total to be returned in a > different format. If I needed the time between 8:30am and 12:00pm added up > i...

copying formula with worksheet name-need new formula to have another worksheet name
I want to be able copy a formula from b5 to b6 but have the worksheet name change, i.e.: I have 13 sheets in my workbook; the first sheet is called Summary which is where my formula presides. 2nd sheet,3rd sheet, etc. are called Sal Hrs_Jan10, Sal Hrs_Feb10, etc. My formula in b5 is =+'Sal Hrs_Jan10'!$C$15. I want b6 to say =+'Sal Hrs_Feb10'!$C$15 I want b7 to say =+'Sal Hrs_Mar10'!$C$15 Column a, cell a5 has Jan-10 (this is a date 1/1/2010 formatted as custom, mmm-yy). Can anyone help me??? Put this in B5: =3DINDIRECT("'Sal Hrs_"...

Access Run-time 07-26-07
Hello all, I have Access 2003, but I want to be able to make systems for people who don't have Access. I have the Access2000 run-time programme. Can I make systems in Access 2003 (saving in 2000 version) which will run on someone else's PC who only has the Access 2000 run-time version? Would it only work with the mdb file, as I can't make Access 2000 mde files with 2003? Would they still be locked out of the code with their run-time versions? Thanks, Franc. See below. "Franc Sutherland" <FrancSutherland@discussions.microsoft.com> wrote in message news:0...

Help with tax formula for marginal tax rates
I am trying to make a single formula that will calculate a person's taxable income assuming the six different marginal tax rates that range from 10% to 35%, as income increases For a single taxpayer, the marginal tax rates and the associated income amounts are: 10% up to $8,350 15% up to $33,950 25% up to $82,250 28% up to $171,550 33% up to $372,950 35% over $372,950 I am using "IF" statements to test for each marginal tax rate. I began the formula as follows, but it returns a value error. Cell D36 is the taxpayer's income =IF(D36<=8350,D36/...

Configuring Exchange 5.5 to use a RBL (Real-time Spam Black List)
Is it possible to use a RBL with Exchange 5.5? What's the simplest way? Don't want to manually enter every email address to block. Thanks Not directly. But majority of antivirus softwares for Exchange support RBL, for example Symantec Mail Security so you could do RBL's in antivirus software -- Regards, Dejan Foro dejan.foro@exchangemaster.net www.exchangemaster.net Join Exchange User Group Europe for free, post questions about Exchange, find useful Exchange links in our link library, chat with other members...See you at www.eugeurope.org/join "Cal_Cougar" ...

Large Quantity of Data, Graphed in Time Intervals
I have a very large set of data (over 20,000 points) listed in minute intervals. I'd like to be able to create charts using different time intervals such as 5 minutes, 15 minutes etc. by using a pull down menu to select the interval and have the graph make itself. Is this possible? You can use a pivot table to group times by such intervals. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kirsten" <Kirsten@discussions.microsoft.com> wrote in message news:811D6E3E-C457-4D36-BA47-1C835D6D670D@microsoft.com....

EXECUTING A FORMULA
I HAVE A PROJECT WHERE I NEED TO CALCULATE TO FIND OUT IF AN EMPLOYEE IS ELIGIBLE FOR A 401K ACCORDING TO THE YEARS WORKED AT THE COMPANY. I HAVE PUT IN THE FORMULA, HIGHLIGHTED THE COLUMNS, BUT WHEN I PRESS ENTER FOR THE CALCULATIONS TO APPEAR, I ALWAYS GET THIS ANSWER ONLY IN THE FIRST CELL $VALUE. SOMEONE PLEASE EXPLAIN TO ME WHAT I'M DOING WRONG... THANKS IN ADVANCE, THERESA In which cell have you put your formula? What is your formula? You appear to have just highlighted the columns and not copied the formula across. Please do not post in all CAPITALS, it is considered rude. ...

Adding Formula to Cell with Data
Column A has hard coded data. I need to add to the hard coded data figure another cell. I do not want to insert a new column or change the format of my worksheet. How can this be done without manually adjusting each cell? =datanumber+CellAddress as in =123+B2 GatorGirl wrote: > Column A has hard coded data. I need to add to the hard coded data figure > another cell. I do not want to insert a new column or change the format of > my worksheet. How can this be done without manually adjusting each cell? > > What type of data in the cells? If numbers and you want ...

Who can explain this LOOKUP formula?
I posted a question on the Excel Worksheet Functions forum the other da and received a formula that works just fine. However, I don't understan why it does what it does... Can anyone explain me why this formula works? > =LOOKUP(2;1/(MID(A2;ROW(INDIRECT("1:1024"));1)= ");ROW(INDIRECT("1:1024"))) In a text string in cell A2 it finds the last space character an returns its position, so I can extract the last word from the string i A2. Thanks anybody for helping me out. Bas -- Message posted from http://www.ExcelForum.com Basz MID(A2;ROW(INDIRECT("1:1024...

How do I format all worksheets in a workbook at one time ?
I want to create a workbook that has all worksheets formatted in the same manner. Is there anyway to do so without formatting each worksheet separately ? yes, Right click on any sheet tab, Select "Select All Sheets" from the pop-up menu. Have fun formatting the active sheet. When done take a look at any sheet and the formatting is done on ALL the selected sheets. Dan Knight "EBrock" wrote: > I want to create a workbook that has all worksheets formatted in the same > manner. Is there anyway to do so without formatting each worksheet > separately ? Dan, l...

Time Problem #3
I've got an excel spreadsheet that tracks attendance (time in/out). We work 8 or 10 hour days depending on the shift. We are required to be as close to 0 hrs over/under at the end of each month however we sometimes carry a balance of x amount of minutes. My goal is to create a cell that you can input your time over/under into which represents any overage/underage from the previous month. This cell will then add into the cumulative +/- total for the current month so that you know when to clock out for the day. My Cumulative +/- cell has this formula =SUM($F$16:F$46+D8) Where column ...

Seeking a solution to a time-consuming problem...
I am hoping someone can recommend a solution for a time-consuming problem that I run into often. I create and modify Excel sheets that are used to record data on a regular basis. Often the modifications done to these sheets require adding or deleting rows and cells in the middle of the sheet (the upper and lower portions are headers with fields and borders, and con not simply be entered in as headers/footers. Herein lies my problem: when adding, removing, and resizing columns in the middle of the sheet the upper and lower portions of the sheet become distorted. I usually end up un-merging,...

Run-time error 40002
I am doing a new install of RMS 2.0 and when I try to run the Department Cost/Sales(Tax-Inclusive) Sales Report I get the 40002 error and the Manager shuts down. Can anyone tell me why this is happening. Thanks! hi Tmac, can you send the content of below mentioned file store at C:\Program Files\Microsoft Retail Management System\Store Operations\Reports\Sales - Department Sales (Tax-Inclusive).qrp or what is the detail of error . This error seems that you have connection problem but if you are only having this problem in the above report then we have to look at the contents, have you...

How to change a color of a cell using a condition formula
I would like to change the color of a cell automatically using a condition formula.....is this possible? Yes, go to Format | Conditional Formatting. -- Regards Juan Pablo Gonz´┐Żlez "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:1330539A-8B3B-44DE-AF70-1E686D172379@microsoft.com... > I would like to change the color of a cell automatically using a condition > formula.....is this possible? Hi see 'Format - Conditional Format' -- Regards Frank Kabel Frankfurt, Germany Carlos wrote: > I would like to change the color of a cell automat...

Formula help #67
I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of rows. The problem is that by dragging the formula, it increments the d130 to d131, d132, etc. How can I make the formula stop incrementing the second variable? $d$130 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "sdmccabe" <sdmccabe@discussions.microsoft.com> wrote in message news:77FE3F27-B67C-4057-855E-C7F2B84FC519@microsoft.com... >I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of > rows. The problem is that by dragging the formula, it inc...

Rounding formula won't copy to other cells in column
I have a formula in cell G2 that reads: =round(F2,0). I'm using this to round the value in cell F2 and remove decimal places. The entire "F" column has values I want to round, and I am unable to "fill" the formula so that I can get the rounded values of each record in the range. What should I be looking for? So what *does* happen when you copy the formula in G2 to other cells in the column? Check to make sure Calculation is set to Automatic (Tools/Options/Calculation). In article <1933354D-C4AE-4051-87BD-29AF728A57DA@microsoft.com>, LindaO <Linda...

Help in creating a formula #2
Hi, Here is my scenario, I am attempting to export data from another program into excel (financial data) that changes daily making my cell location for totals change is there a formula that can combat that??? to eliminate data entry time!!! :P Hi Place your totals into table header, and calculate them using dynamic ranges. An example: Your data are on sheet MyData in range A5:E5 and down. In range A4:E4 are column headers. Rows 1:3 are empty. Column A contains some identifier (Name or Date etc.) and is never is empty, whenever there are any data in row. Columns C:E contain numeric data...

Getting #VALUE! from formula that contains range
I have the following problem: I need a formula wherein a summation happens of different IF's. When I make the formula in the edit box (function arguments ('Fx' in formula bar)) I see the result of my formula. But in the cell the result is #VALUE! . I tried something similar but easier and the same happens, I give the example formula: =SUM((A1:D1)*(A2:D2)) What can I do about it or is it just happening to me? Hope somebody van help me, thanks a lot! Jan Read help on SUMPRODUCT -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jan" <jdridd...

IF formula
See attachment: How can i write the same IF formula if "x" is marked for 1.1, 1.4, an 1.9 also in the same cell?? The "x" will only be marked for one at th same time....but i never know witch of them. Therefore I want on formula that covers them all. Phoeni Attachment filename: if formula.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46184 -- Message posted from http://www.ExcelForum.com Phoenix, =H4*I4*IF(ISNA(MATCH("x",J4:M4)),1,INDEX(J3:M3,MATCH("x",J4:M4))) (I'm not sure whether the , ...