Pasting time values will not sum total, typing will.

[Caveman1964]

Hi,
I have searched for this issues and can't find. I am sure its here somewhere.
Problem;
-I have cells formatted [h]:mm. The rows and columns total the time in another cell.
-I have to copy and paste values from another sheet.
-When I paste the values in the cells, the values default to left side of cell and do not calculate the sum.
-I can type the same value in, the value then moves to right of cell and the sum calculation works.
My attempts to resolve;
I put calculation in automatic. Didn't help
I made a macro to paste and format after paste. Didn't help
The only resolve is manually typing the same value into the cell, then the sum works.
If a macro can help, I can use that. I tried multiple to no avail.
Appreciate ahead of time any help.
Thanks

 

[etaf]

left justification is TEXT and right is a number
so when you copy into the cell , its seeing the value of 8:00 as TEXT and not as a time number , which is 0.3333333333 and then you format to display as time

you have other things in the column - but if you select the column and then
if you do a DATA> Text to columns
will that convert to real time

-I have to copy and paste values from another sheet.

how are they shown in the other sheet

change format to general - do the times change to decimal numbers

 

[Caveman1964]

left justification is TEXT and right is a number
so when you copy into the cell , its seeing the value of 8:00 as TEXT and not as a time number , which is 0.3333333333 and then you format to display as time

you have other things in the column - but if you select the column and then
if you do a DATA> Text to columns
will that convert to real time

how are they shown in the other sheet

change format to general - do the times change to decimal numbers

Ok thanks! The gives me something to work with.
The format imports as general, and left aligned.
I'll try some formatting with the import.

 

[etaf]

left aligned is TEXT , and General is text - when you click on format , you will see 08:00 displayed , rather than 0.333333 in the format window

 

[Caveman1964]

left aligned is TEXT , and General is text - when you click on format , you will see 08:00 displayed , rather than 0.333333 in the format window

Ok thanks!
For some reason, I can format on the import but it doesn't adjust the value without typing it in.
I'll work with it some more. So far, no matter what I try, it will not adjust as it should without typing it in.
Your time and effort is much appreciated.

 

[etaf]

you are welcome

pretty certain its pasting as text

did you check , the source as mentioned in an earlier post

 

[Caveman1964]

you are welcome

pretty certain its pasting as text

did you check , the source as mentioned in an earlier post

I checked the source and it is in "general". I changed the source to "text", still did not work, i changed to "time", didn't work.
Not sure whats happening. I will work on it tomorrow. My approach has been to methodically copy and paste with varying formats, specifically what you pointed out.
Its odd how it is formatted as should be but will not calculate until I retype. I'll create a macro tomorrow to format before pasting as this is the only thing left I believe. Even though its already formatted.
Thanks so much for the help today. I am sure I'm missing one tiny detail.

 

[Joe4]

I checked the source and it is in "general". I changed the source to "text", still did not work, i changed to "time", didn't work.

The Format only changes how a value is displayed. It will not alter/change any existing values in cell.
If has been entered as "Text", changing the format after the fact to "General" or "Number" will not change the value - you must re-enter (or edit) it.

You can do that by:
1. manually re-typing it;
- or -
2. selecting the cell, hitting F2 to get into edit mode, and hitting enter;
- or -
3. if you have a whole column of data to do at once, you can use "Text to Columns" found under the "Data" menu to re-enter all the columns values at once.

 

[Caveman1964]

The Format only changes how a value is displayed. It will not alter/change any existing values in cell.
If has been entered as "Text", changing the format after the fact to "General" or "Number" will not change the value - you must re-enter (or edit) it.

You can do that by:
1. manually re-typing it;
- or -
2. selecting the cell, hitting F2 to get into edit mode, and hitting enter;
- or -
3. if you have a whole column of data to do at once, you can use "Text to Columns" found under the "Data" menu to re-enter all the columns values at once.

Ok Joe...I was about to walk out door and saw this message.
The text to columns worked perfectly. F2 does as well...the text to columns will be my easiest resolve.
For some reason, I have never had to use this function before.
Learned something today from two of you guys!
Thanks to all!

 

[Joe4]

You are welcome!
Glad we were able to help.

Yes, "Text to Columns" is a great tool for things like changing the data type, or splitting the data up at some delimiter or fixed position.