truncate date; nix the time component
 

I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))

truncate date; nix the time component
 

In a formula, you can use TRUNC to get only the date portion of a
datetime. E.g., =TRUNC(A1,0)

In code, you can use the Int (not CInt) function. E.g,

Dim L As Long
L = Int(Range("A1").Value)

Remember that dates are nothing but numbers, so you can manipulate
them in any fashion you would a "real" number.

The reason you don't find the Date function in WorksheetFunctions is
because Excel functions that have a native VBA function aren't
included in WorksheetFunctions. Since VBA has DateSerial, the Date
function isn't included.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Sun, 28 Feb 2010 13:48:10 -0800 (PST), cate
wrote:

I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))

truncate date; nix the time component
 

Try: =Today()



"cate" wrote in message
...
I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))

truncate date; nix the time component
 

In VBA it would be
Sub dk()
myDate = Date
MsgBox myDate
End Sub

"cate" wrote in message
...
I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))

truncate date; nix the time component
 

Not entirely sure of what you are attempting to achieve. That is do you want
VBA code or formula for a worksheet?

Worksheet function for date only is TODAY() while in VBA it is Date.

NOW() function is date and time for both Worksheet and VBA.

To convert a Date/Time to date only in VBA

Dim myDate as Date
myDate = DateValue(Format(Now(), "dd mmm yyyy"))

Can replace Now() with a cell reference containing Date and Time and also,
depending on your local date format, you might want to use format
"mmm dd yyyy". When using DateValue, it is best to use the literal month
(mmm) rather than numeric.

Hope this helps

--
Regards,

OssieMac


"cate" wrote:

I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))
.

truncate date; nix the time component
 

You might be looking for this:

=Text(Now(), "mm/dd/yyyy")

Which give the date as a string.


"cate" wrote in message
...
I am reading a cell that was populated with NOW(). It contains a time
part I do not want. There has got to be a trunc() function somewhere,
but I can't find it. Was going to try WorksheetFunction.Date, but
it's not there? (I thought that anything available for use on the
sheet would show up here)

So, do I build a string and use DateTime.DateValue, or DateSerial, to
get just the date part and eliminate the time component? It works ok,
but it just doesn't seem like it's the way to do it.

Thank you.

Here is one example of removing time. Is this the proper way to do
it? (will be 'vba'd)

http://www.techonthenet.com/excel/qu...emove_time.php
Excel: Convert date/time value to a date value (remove time portion)
in Excel 2003/XP/2000/97
Answer: In order for Excel to recognize the values as dates, you will
need to modify your formula as follows:

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1))