Microsoft Office 2007 professional (aca...

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

balaz

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 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 hrsDo 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 hrsI used cells D2 and D3 for this test

Enter =SUM(D2:D3) in another cell

and it shows 95 hrsIf 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.

Regards

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")

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.

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.

Ask Your Question

Weekly Poll