Need a method to extract the Date from a cell to a new cel

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Need a method to extract the Date from a cell to a new
cel. the curent value of two cells are:
10/31/2003 3:34:48 PM
8/22/2003 7:29:08 PM

While I am at it, how do I extract the time also
 
Put the dates into column A and run this code

Private Sub CommandButton1_Click()
a = 1
Do While Range("A" & a) <> ""

For x = 1 To Len(Range("A" & a))

If Mid(Range("A" & a), x, 1) = "/" Then y = y + 1
If y = 2 Then Exit For

Next x
y = 0
Dim z As Integer
Range("c" & a) = Left(Range("a" & a), x + 4)
z = Len(Range("A" & a))
Range("e" & a) = Right(Range("a" & a), z - x - 4)

a = a + 1
Loop

End Sub
 
Excel stores dates as whole numbers and times as decimal fractions. To extract date and time from a cell you need to separate the
whole number and the fractional part.

If you want formulas for this and assuming that your date/time value is in A1:

=INT(A1) for the date part
=MOD(A1, 1) for the time part

If you want VBA code

Range("B1").Value = Int(Range("A1").Value 'for the date
Range("B2").Value = Range("A1").Value - Int(Range("A1").Value 'for the time part

There is a Mod operator in VBA, but it does not return fractional values.
 
This works too:

=DATE(YEAR(A1),MONTH(A1),DAY(A1))
=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

Shockley
 
Good point. It's probably more readily understood by most users than my approach.
 
Back
Top