critical SUM in excel

Microsoft Office 2007 professional (aca...
March 3, 2010 at 03:10:35
Specs: Windows XP
 In A1 cell value is 60 HrsIn B2 cell value is 35 Hrsi want to add and show it in C3 cell.the problem is not adding because it have both text and numbers. Also I cannot delete the text from the cell, but i want to sum it. I am too confused. Please help me guys.Thanks in advancebalaz

See More: critical SUM in excel

#1
March 3, 2010 at 04:43:47
 Hi,What you need to do is to replace the mixed number plus text with a pure number and then format it to show 60 hrs etc.Excel stores times as fractions of a day in the decimal part of a number (it also uses the integer or whole number part to store days).60 hours is 2 days plus 12 hours. 12 hours is half a day, or .5So 60 hours is 2.5 in Excel's date/time systemGo to an unused cell and enter 2.5Right-click the cell and choose Format cells...Select the Number TabChoose Custom from the bottom of the listIn the Type: box on the right enter [h] "hrs" (replaces General)Click OK and your cell will now show 60 hrsDo the same for 35 hrsJust to show how this works enter =35/24 in an unused cellRepeat the formating and the cell will show 35 hrsI used cells D2 and D3 for this testEnter =SUM(D2:D3) in another celland it shows 95 hrsIf it shows 3.9583, just repeat the cell formatingBTW the [h] forces Excel to show hours beyond 24.using h will show hours up to 23 and the excess will be held as days, but not shown.When you click on cell D2 notice that the formula bar shows 02/01/1900 12:00:00 PM because Excel has 2.5 days and as its Date system starts with 1 for 01 January 1900, you see half way through 02 January 1900.Anyway just copy you newly formatted cells to where you want them.Regards

Report •

#2
March 3, 2010 at 06:14:02
 You can "extract" the numbers from the cells.Type this into C3:`=SUM(LEFT(A1,FIND(" ",A1)-1),LEFT(B2,FIND(" ",B2)-1))`That should give you 95, as long as there is a space between the Number and "Hrs".If you want the display to read as "95 Hrs", type this into C3:`=CONCATENATE(SUM(LEFT(A1,FIND(" ",A1)-1),LEFT(B2,FIND(" ",B2)-1))," Hrs")`

Report •

#3
March 5, 2010 at 23:32:21
 hi lantree,what u did is a marvellous & excellent job. this reduce my burden.I also want to know another solution from you. i want to attach a excel file. can u pls give me ur e-mail id.

Report •

Related Solutions

#4
March 18, 2010 at 05:09:24