Trim leading numbers

[Vacuum Sealed]

Hi All

I just love the challange of fixing up someone elses dogs breakfast.

I have to sum a total amount of mins from 2 different time formatted cells.

I have no idea why it was setup this way, suffice to say my calls for it to
be changed have fallen on deaf ears by the brains trust, so here I am!

Column ("M").NumberFormat = "hh:mm:ss AM/PM"
It contains only Time

Column ("N").NumberFormat = "mm/dd/yyy hh:mm:ss AM/PM"
It contains Date/Time

With Column ("N")
1. Need to change format to a number. (copy to temp location)
2. Trim the 5 Leading numbers leaving only the decimal portion
3. Paste values from temp location bank to ("N")
4. change format to match Column("M")

TIA
Mick.

 

[Claus Busch]

Hi Mick,

Am Sun, 3 Jul 2011 15:46:57 +1000 schrieb Vacuum Sealed:

With Column ("N")
1. Need to change format to a number. (copy to temp location)
2. Trim the 5 Leading numbers leaving only the decimal portion
3. Paste values from temp location bank to ("N")
4. change format to match Column("M")

try:

With ActiveSheet
LRow = .Cells(.Rows.Count, "N").End(xlUp).Row
'write decimal part to column Z
'change 2 to 1 if there's no header
.[Z2].Formula = "=MOD(N2,1)"
.[Z2].AutoFill .Range("Z2:Z" & LRow)

.Range("Z2:Z" & LRow).Copy
.Range("N2").PasteSpecial xlPasteValues
.Range("N2:N" & LRow).NumberFormat = "hh:mm:ss AM/PM"
.Range("Z2:Z" & LRow).ClearContents
End With

Regards
Claus Busch

 

[Vacuum Sealed]

Hi Claus

This is sweet, thx heaps.

Cheers
Mick.

 

[Ron Rosenfeld]

Hi All

I just love the challange of fixing up someone elses dogs breakfast.

I have to sum a total amount of mins from 2 different time formatted cells.

I have no idea why it was setup this way, suffice to say my calls for it to
be changed have fallen on deaf ears by the brains trust, so here I am!

Column ("M").NumberFormat = "hh:mm:ss AM/PM"
It contains only Time

Column ("N").NumberFormat = "mm/dd/yyy hh:mm:ss AM/PM"
It contains Date/Time

With Column ("N")
1. Need to change format to a number. (copy to temp location)
2. Trim the 5 Leading numbers leaving only the decimal portion
3. Paste values from temp location bank to ("N")
4. change format to match Column("M")

TIA
Mick.

As an aside, you can sum just the minutes in column N (or any column for that matter), with this array formula, and without changing either the formatting or the contents.



This formula must be **array-entered**:

=SUM(MOD(N1:N1000,1))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

When you change the contents of column N, I would suggest this method:

====================================
Sub StripDates()
Dim rg As Range
Dim v As Variant
Dim i As Long

Set rg = Range("N1", Cells(Cells.Rows.Count, "N").End(xlUp))

v = rg
For i = LBound(v) To UBound(v)
v(i, 1) = v(i, 1) - Int(v(i, 1))
Next i
rg = v

rg.NumberFormat = "hh:mm:ss AM/PM"

End Sub
==========================

 

[Vacuum Sealed]

Thx Ron

That works really well.

Cheers
Mick.

 

[Rick Rothstein]

I just love the challange of fixing up someone elses dogs breakfast.

I have to sum a total amount of mins from 2 different time formatted
cells.

I have no idea why it was setup this way, suffice to say my calls for it
to
be changed have fallen on deaf ears by the brains trust, so here I am!

Column("M").NumberFormat = "hh:mm:ss AM/PM"
It contains only Time

Column("N").NumberFormat = "mm/dd/yyy hh:mm:ss AM/PM"
It contains Date/Time

With Column("N")
1. Need to change format to a number. (copy to temp location)
2. Trim the 5 Leading numbers leaving only the decimal portion
3. Paste values from temp location bank to ("N")
4. change format to match Column("M")

Assuming Column M is fully formatted as you message indicates it is, then I
think this non-looping code should do what you asked for...

Sub ChangeDateTimeToTimeOnly()
Columns("N").NumberFormat = "General"
Columns("N").Replace "*.", ".", xlPart
Columns("N").NumberFormat = Columns("M").NumberFormat
End Sub

Rick Rothstein (MVP - Excel)

 

[Vacuum Sealed]

Thx Rick

As a footnote, I managed to plead out my case with this one, being a
hand-me-down dogs breakfast of a file.

The brains-trust conceded and have allowed me to update and change most of
the way it is structured and the way data is imported, so I don't have to
convert rdundant time/date columns.

Thx again.
Mick.