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.

 

[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.