timevalue across 2 colums

susa

New Member
Joined
Oct 2, 2006
Messages
9
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

susa

New Member
Joined
Oct 2, 2006
Messages
9
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)

2aadk4k.jpg
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If your "Times" are true Times in Excel your formula should work.
Just format the Total cell as [h]:mm.
 

susa

New Member
Joined
Oct 2, 2006
Messages
9

ADVERTISEMENT

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

2m7ampd.jpg
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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

New Member
Joined
Oct 2, 2006
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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

New Member
Joined
Oct 2, 2006
Messages
9
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

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,656
Messages
5,660,153
Members
418,555
Latest member
Ike Andoit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top