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 Hrs
In B2 cell value is 35 Hrs
i 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 advance

See More: critical SUM in excel

Report •

March 3, 2010 at 04:43:47

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 .5

So 60 hours is 2.5 in Excel's date/time system

Go to an unused cell and enter 2.5
Right-click the cell and choose Format cells...
Select the Number Tab
Choose Custom from the bottom of the list
In the Type: box on the right enter [h] "hrs" (replaces General)
Click OK and your cell will now show 60 hrs

Do the same for 35 hrs
Just to show how this works enter =35/24 in an unused cell
Repeat the formating and the cell will show 35 hrs

I used cells D2 and D3 for this test
Enter =SUM(D2:D3) in another cell
and it shows 95 hrs

If it shows 3.9583, just repeat the cell formating
BTW 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.


Report •

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 •

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

March 18, 2010 at 05:09:24
Sorry for not replying sooner.

For some reason, my browser didn't show your reply.

I sent you my email address via the Message Center.

Report •

Ask Question