Now() function with time trim

Question

So the function =Now()....is there a way I can use this and only get the date, not the time?

or is there just a function for this idea?

score 34 · Accepted Answer · answered Jun 24 '10 at 01:13

34

There is a Date function.

answered Jun 24 '10 at 01:13

tlayton

1,757
1
11
13

2

so how do I use this?? i can use =Now() in VBA to get the current date and time, but it will not allow me to enter Date() or =Date(). it automatically takes away the () and the function does not seem to work the same way as =Now() – Justin Jun 24 '10 at 03:06
Date is the correct function to use. And yes, in the code editor, it will remove the () – Albert D. Kallal Jun 24 '10 at 03:33
1

ah i see...i stand corrected. mistake else where was throwing me off. apologies...and thanks! – Justin Jun 24 '10 at 03:47

score 12 · Answer 2 · edited Sep 08 '13 at 04:30

12

Dates in VBA are just floating point numbers, where the integer part represents the date and the fraction part represents the time. So in addition to using the Date function as tlayton says (to get the current date) you can also cast a date value to a integer to get the date-part from an arbitrary date: Int(myDateValue).

edited Sep 08 '13 at 04:30

HansUp

95,961
11
77
135

answered Jun 24 '10 at 01:22

Dean Harding

71,468
13
145
180

Why would anyone do that when there's a function specifically defined to return the integer part alone? That is, why make two function calls when one will do the job? – David-W-Fenton Jun 24 '10 at 21:12
3

I interpret Dean's suggestion as a way to understand the nature of Date/Time values, not as a general substitute for `Date()`. From that standpoint I think his suggestion is very useful. +1 – HansUp May 03 '12 at 13:52

score 11 · Answer 3 · edited Sep 30 '12 at 02:32

11

DateValue(CStr(Now()))

That's the best I've found. If you have the date as a string already you can just do:

DateValue("12/04/2012 04:56:15")

or

DateValue(*DateStringHere*)

Hope this helps someone...

edited Sep 30 '12 at 02:32

andrewsi

10,807
132
35
51

answered Aug 08 '12 at 11:16

Nick

111
1
2

score 5 · Answer 4 · answered Aug 15 '13 at 08:03

I would prefer to make a function that doesn't work with strings:

'---------------------------------------------------------------------------------------
' Procedure : RemoveTimeFromDate
' Author    : berend.nieuwhof
' Date      : 15-8-2013
' Purpose   : removes the time part of a String and returns the date as a date
'---------------------------------------------------------------------------------------
'
Public Function RemoveTimeFromDate(DateTime As Date) As Date


    Dim dblNumber As Double

    RemoveTimeFromDate = CDate(Floor(CDbl(DateTime)))

End Function

Private Function Floor(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double
    Floor = Int(x / Factor) * Factor
End Function

score 3 · Answer 5 · answered Jun 24 '10 at 01:20

3

You could also use Format$(Now(), "Short Date") or whatever date format you want. Be aware, this function will return the Date as a string, so using Date() is a better approach.

answered Jun 24 '10 at 01:20

Rob Gray

3,186
4
33
34

what would i need to do to the above to get a date format of 28-Sep-10? Thanks I appreciate it! – Justin Jun 24 '10 at 03:11
Format$(Date(), "dd-MMM-yy"). There is also the Format function which returns a variant. If you know you're going to need a string to be returned, use the Format$ function. – Rob Gray Jun 24 '10 at 03:35
In code, you can generally use # to surround the date. Eg: #09/28/2010# However, the above can be messed up by date formatting, so another choice is dateserial() command. The syntax is dateserial(year,month,day), so DateSerial(2010,9,28) – Albert D. Kallal Jun 24 '10 at 03:36
wow thanks guys! i learned a lot with a simple question!! i love to do so because I am an aspiring student! thanks very much! – Justin Jun 24 '10 at 03:42
1

You can also restore the date datatype from a string representation of a date by passing it through the DateValue() function. But this question is completely wrongheaded in recommending using Format() just to get the date part. – David-W-Fenton Jun 24 '10 at 21:13

score 1 · Answer 6 · answered Apr 05 '16 at 08:27

Paste this function in your Module and use it as like formula

Public Function format_date(t As String)
    format_date = Format(t, "YYYY-MM-DD")
End Function

for example in Cell A1 apply this formula

=format_date(now())

it will return in YYYY-MM-DD format. Change any format (year month date) as your wish.

Now() function with time trim

6 Answers6

Linked

Related