Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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






"Paul" wrote in message
...
Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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

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.

--

John Green - Excel MVP
Sydney
Australia


"Paul" wrote in message ...
Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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

Not forgetting the closing parentheses on the Int function, as I did.

--

John Green - Excel MVP
Sydney
Australia


"John Green" wrote in message ...
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.

--

John Green - Excel MVP
Sydney
Australia


"Paul" wrote in message ...
Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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

This works too:

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

Shockley





"Paul" wrote in message
...
Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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

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

--

John Green - Excel MVP
Sydney
Australia


"shockley" wrote in message ...
This works too:

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

Shockley





"Paul" wrote in message
...
Need a method to extract the Date from a cell to a new
cel. the curent value of two cells a
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