Saturday, October 13, 2012

What are Date and Time Functions In VBA

The Date and Time Functions in VBA:
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,Tamatam

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog