Saturday, 13 October 2012

Date and Time Functions In VBA


The Date and Time Functions:


Function
Description
Now

This Function Returns Current date and time.
Example:
1/7/12 5:16:38 pm returned by Now



Date


This Function Returns Current date only.
Example:
1/7/12 returned by Date


Time



This Function Returns Current time only.
Example:
5:16:38 pm returned by Time

Timer

This Function Returns Number Of Seconds Since Midnight.

Example:
5:16:38 Pm Returned By Timer

Timevalue()

This Function Returns Time part of argument.

Example:
5:16:38 pm returned by Timevalue(Now)


Datevalue()

This Function Returns Date part of argument (Used for ordering by date)
Example:
select * from TblPeople order by datevalue(review)


Dateserial()

This Function Returns Date Part Of Three Arguments: Year, Month, Day

Example:
Having Invoicedate <= Dateserial(Year(Now), Month(Now)-1, Day(Now))

Dateserial Handles January Correctly In The Above Example


Datepart()

This Function Returns Returns A Portion Of The Date.
Year example:
2012 Returned By Datepart('YYYY', Date)

Month Example:
10 returned By Datepart('M', #10/11/2012#)

Week Of Year Example:
41 returned by Datepart('ww', #10/11/2012#)

Day Of Week Example:
Monday Returned By Datepart('D', #6/3/2012#)

Quarter example:
4 returned by Datepart('Q', #10/11/2012#)


Year()

This Function Returns the Year Portion of the date argument.


Month()

This Function Returns the Month Portion of the date argument.


Day()

This Function Returns the Day Portion of the date argument.


Monthname()

This Function Used to Format Month Names.
July returned by Monthname(month(date))


Weekdayname()

This Function Used To Format Day Names.
Wednesday returned by WeekdayName(Weekday(Date))



Today()


This Function  Returns Current date only; used in Excel, not available in Access.
Datediff()

This Function  Returns The Difference In Dates.

Days example:
-656 returned by Datediff("D", #10/11/2001#, #12/25/1999#)

Months Example:
1 returned by Datediff("M", #8/10/2000#, #9/14/2012#)

Days Example:
0 returned by Datediff("M", date1, date2)
0 is returned above only if the two dates have same month and year


Dateadd()

This Function Add and Subtract Dates.

10/11/2002 returned by Dateadd("yyyy", 1, #10/11/2012#))

Today's Date + 30 Days Returned By Dateadd("d", 30, date)

The date 45 days ago returned by Dateadd("D", -45, Date)

To Find Monday Of A Week:
Dateadd("D",-Weekday(Date)+2,Date)


Format()

This Function Very useful for Formatting Dates.
Examples:

Wed, 5-July-2012 Returned By Format(Date,"DD-MMMM-YYYY")
5-Jul-12  Returned By Format(Date,"D-MM-YY")



Thanks.,
TPR

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts