SUBTOTAL don’t add up when using Paste Special Value for time values

Hi Guys,

I am having a problem with Excel. I have a source spreadsheet that I need to reconcile with hours calculated against an hour pay rate.

What I do is copy all of the hours which is in the normal time format and do a ‘Paste Special Values’ and paste this data in my own reconciliation worksheet.

The sums add up per row ok, for example if Row 22 had 3:15 and the Pay Rate is £12 per hour. (I have a separate hidden column that converts the time to decimal) I get £117. Now this works fine and is adding up ok.

The problem I am having is that at the top of the time column there is a Total line that totals up all of the times in that column. Now this normally works ok if I manually type in the numbers but when I ‘Paste Special’ and select the values option. The total at the top is 0 and does not add up all of the individual times in the column. When I use paste special value when pasting monetary values in column Q it adds up fine.

The formula used for each of the totals at the top of each column is: (The F7:F100 in the code below refers to the time column )

Please Login or Register  to view this content.

I have to individually either hit F2 and then TAB or double click each cell and then TAB and then the Totals will start to add up. I have hit F9 to recalculate thinking that would help but nothing.

Can anyone tell me what the cause of this is please?

Thanks in advance.

I managed to fix the problem thanks to dominicb's excellent Excel addin and I used the option for 'Covert Text to Numbers' and this did the trick.

I did notice one thing; the formatting on my spreadsheet for time is "[h]:mm". However when I looked at the cell in original worksheet it is formatted thus: even though the cell displays 35:00 when you select the cell, the actual figure in the formula toolbar is 01/01/1900 11:00:00 it is probably because this is pulled from a report in a database and exported to Excel. How can I fix this?

Thanks in advance.

EDIT: Is there a way to exclude the date and convert this to time only?