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
 

susa

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

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

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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

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

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

Forum statistics

Threads
1,141,297
Messages
5,705,559
Members
421,399
Latest member
hjweiss00

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