timevalue across 2 colums

[susa]

I have a xls file where a timevalue, in hours and minutes is across two columns (L, M)

visually it looks good, there is no space in the field but somehow excel thinks the part before the colon in L and part after is M, while you can't see that in looking at the file

the values look like 1:00 or 2:45 or 6:15
there are about 20 lines per group and the desire would be to total the hours and minutes but it does not work as the data is somehow across two columns

have attempted to copy and paste, that also fails with some message about unequal formatting or such. I really don't want to retype this data, as it's an export file from another system but would like to hear some suggestions for

1) how to combine, either via vba or manually the columns L and M and
2) how to sum the hours into a HH:MM format. the number for a month can be large, like 1400:40 would be 1400 hours and 40 minutes

 

[susa]

thank you for the offer but I can't send it out. I even checked the cells to ensure they are not locked (they are not) and each of the L15 - L63 is formatted as hh:mm

no problem there

the problem is that the =sum(L15:L63) shows 00:00 and it also is not locked and is formatted as hh:mm

 

[Datsmart]

A quick way to see if your TIME is a number is to enter this formula in a cell on your page.
"=ISNUMBER(A1)"
If the result is "TRUE", A1 contains a number, if "FALSE", it is Text.

 

[babycody]

Let's start over one more time. Open a new workbook. Only type in 6:30 in A1 and 5:30 in A2. No copy and pasting. Do not format these. In B1 type =sum(A:A) Then format B1 with the custom format [h]:mm. Let me know if you get 12 or not. If that works then type more times in column A.

 

[susa]

it appears I can individually select each of the cells and make them h:mm but the .xls itself contains a blank row between each row of data and maybe that is what's causing it not to sum the L15:L63 ?

any automatic way to kill blank rows or to not compute them?

 

[Datsmart]

I tested adding blank rows between the numbers, the correct SUM did not change, still getting 86:15.
If you select cell L15, do you get "6:30:00 AM" in the formula bar? If not, your time is Text, not true time.
If you drag L15 to M15 it should increment to 7:30 if it is true time. It will increment to 6:31 if it is text.

 

[susa]

yes, I can manually enter a couple times and sum them just fine, the mystery is the .xls file where something prevents this very simple function

 

[Datsmart]

Is it all pages of that workbook or just the one?
I would check out the "worksheet" module and the "This WorkBook" module for some kind of event code.
Might also look for any conditional formatting on that page.

 

[susa]

babycody wrote:
>Would you care to email me the workbook?

just did

note that I removed all title data but did not modify in any way the rows that you see and the *Duration* column is the one I wanted to sum for all days, there are 2 days listed, each with varying rows. The data is locked which you can unlock when you format the cells.

many thanks

 

[babycody]

I fixed it for you. You have a leading space infront of some of your time. i.e. (space)9:00. I am not aware of how you imported this information so I can't give you any advice there. I used a formula to remove the space, and then converted it back to a number. Then I formatted as time (13:30). I had to put an error trap on the formula because of the blank rows you have between the data. I custom formatted the sums with [h]:mm. It would be a much better solution to figure out how you are importing that leading space and prevent that from happening.