Excel summing up text

Hello,
I have created the first three columns with =TEXT(MONTH($B1),"00"), =TEXT(DAY($B1),"00"), and  =TEXT(YEAR($B1),"00")
07      17      2014      0717
The fourth column is simply combining the text; =$B$1&$C$1
I have several of these.  Now I need to add them all up.  How do i add "text"?  The text are numbers.
For example:
0717
0718
0719
etc.

Thanks
chimaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HAJ2014Commented:
=sum(b1:b5)  

It will add everything in between those cells  b1,b2,b3,b4,b5.



Thanks,

Henry
0
Randy PooleCommented:
=VALUE($B$1)+VALUE($C$1)
0
chimaAuthor Commented:
HA2014 (short 14), yeah one would link so, this is the first thing I tried, but it did not work.  Did you try it?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chimaAuthor Commented:
Randy, you might be on the right path; =sum(VALUE($B$1:$B$5)  I'll go try it.
0
chimaAuthor Commented:
Just in case Randy got it so quickly, may i ask another question with this one?  How can I "associate" two or more tabs within the same "worksheet," so that if one tab is move the other will follow?  Or, make two or more tabs look/act as one tab?  In other words; "joining tabs"?
0
Randy PooleCommented:
Elaborate on tabs, do you mean 2 worksheets in the same workbook?
0
chimaAuthor Commented:
Randy, Oops!, you are right 2 worksheets in the same workbook?

In answer to adding text, again you are right.  I had to add VALUE() to each text cell, then do the summation.
This works, but for my purpose it drops the 0 between two numbers;
=TEXT(MONTH($B1),"00") has 07
=TEXT(DAY($B1),"00") has 17
and  =TEXT(YEAR($B1),"00") has 2014
07      17      2014    

The fourth column is simply combining the text; =$B$1&$C$1 has  0717
When I use VALUE() it convert it to 717, which this is okay, but when i get to August, 0801 is converted to 81 and I need 801.

 May I ask the question this way.  I have a second sheet/tab and I use this ='62 Max Days'!E1
E1 has this =$B$1&$C$1  
E1 is in the first worksheet,  so this is okay =VALUE($B$1)&VALUE($C$1), but the zeroes are dropped.
And I can't do this; =VALUE('62 Max Days'!E1)  nor can I do this ='62 Max Days'!VALUE(E1)
And this does not work; ='62 Max Days'!(VALUE(E1))

Comments?
0
chimaAuthor Commented:
Excel-tab-association-tie.PNGThese are the two tabs I want to associate.  In the first sheet I have my data and in the second I do some calculations.
I cannot combine the two, because (to my way of thinking) on the first sheet I can click on the upper left button to highlight ALL of the rows and columns.  If I had calculations there, I would grab those, which I do not wish to do so.
0
Randy PooleCommented:
Not sure on the association of 2 sheets, never heard that before.  As for the value you would do a value of
=VALUE($B$1&$C$1) which would omit the leading zero but should work fine on the 08 example you posted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chimaAuthor Commented:
Randy, one would hope.  Tried it and this is what I get;
Each cell is a separate value and drops the leading zero
0
Rob HensonFinance AnalystCommented:
To overcome adding up the text values, assuming your B1&C1 formulas are in column D, use:

=SUM(D1:D5*1)  Confirm with Shift Ctrl & Enter, this will add {   } at either end of the formula. This will convert 0717 to 717 and 0801 to 801.

This will do the same as Randy's suggestion:

=SUM(VALUE($B$1:$B$5))

but will also need entering with Shift Ctrl & Enter.

On a broader note, what are you trying to acheive by adding up your date values? If you want to determine number of days between two dates you can just subtract earlier date from later date and then format as a number rather than a date.

Thanks
Rob H
0
chimaAuthor Commented:
Rob and Randy, I apologize for the delay...off on a long weekend.  Rob I will try your suggestion and get back to you.
I'm testing this application which manages days and the data for each day.  I am simply associating a date with data indicating which date it is.  See you before the morning is over.
0
chimaAuthor Commented:
Well, I need to apologize to Randy.  When I tired VALUE($B$1&$B$5) I did not see that it actually worked.  I must have done something wrong.  Problem solved, I'll issue the points shortly.
0
chimaAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.