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

 

[babycody]

You will need to use a custom format [h]:mm to sum the time values. Hope this is what you are looking for.

Book1
	A	B	C	D	E
1			Format as time		Sum
2	2	45	2:45		49:00
3	4	15	4:15		[h]:mm
4	2	45	2:45
5	4	15	4:15
6	2	45	2:45
7	4	15	4:15
8	2	45	2:45
9	4	15	4:15
10	2	45	2:45
11	4	15	4:15
12	2	45	2:45
13	4	15	4:15
14	2	45	2:45
15	4	15	4:15
Sheet1

 

[susa]

hmm... that does not work or I don't know what I'm doing

to recap, the COLON : is there, in the data, it's not missing unlike the picture posted above

I've now managed to clean out the offending column M and all numbers are in L, so L19:L63 should work but the format is wrong

total timevalue should read 86:15 for =SUM(L15:L63)

 

[Datsmart]

If your "Times" are true Times in Excel your formula should work.
Just format the Total cell as [h]:mm.

 

[susa]

I selected L15 through L63, format cells and selected time as hh:mm and I formatted total cell as h:mm, also tried in custom as [h]:mm

still, the sum on the bottom shows 00:00

I tried it manually by entering all the values in a new .xls file, formatted with every combination of h:mm and the sum the same, still zeroes

 

[babycody]

When you mentioned that you cleaned up your data earlier how did you do that. I ask because it sounds like your time is formatted as text. Are the times you posted above a product of a formula? I know you said you changed the formatting to hh:mm, but that won't help if you used certain functions to get this list of times.

 

[susa]

as you can see I posted a pic of manually entering each value, formatted as time h:mm and I tried [h]:mm, same results

the data in the new .xls is in time format, the sum for those columns still does not add up to 86:15

 

[babycody]

In your picture above you will notice that all of the times that you entered are on the left side of the cell. If they were numbers then they would be on the right side of the cell. One more reason to believe that they are formatted as text somehow.

 

[susa]

I re-entered, all values, selected all rows, format as time and even went into alignment and selected right alignment, previously set to center or left

I can pick any one value, look at format and it shows time

 

[babycody]

Would you care to email me the workbook? I can PM you my email address. Changing the alignment doesn't effect anything. Excel naturally aligns text to the left and numbers to the right. Being a member for a while has taught me to look for this. It can be an indication of what is going wrong.