Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
msWinSck.ocx: Run-Time Error 429 ActiveX Component can't create ob | Excel Programming | |||
Date Picker component | Excel Programming | |||
Run time '429' : ActveX component can't create object | Excel Programming | |||
How to truncate date | Excel Worksheet Functions | |||
Truncate Cell text all the time | Excel Programming |