## Excel Date Functions

Categories: How-To

In the information jungle for Excel, when it comes to find certain “How-to” with Excel, it is like finding a “needle in Mount Everest”.

Here is the list of functions that you can use when using ODBC + SQL + Excel

Again, the following stuff applies only when using SQL with Excel over ODBC connection.

One of the important function is Dateserial which converts to a date type given the year, month and day.

 . Excel date function string . Last Day of the month dateserial(year(now()),month(now())+1,0) . First Day of month dateserial(year(now()),month(now()),1) . First day of year dateserial(year(now()),1,1) . Last day of year dateserial(year(now()),13,0) . Week of year datepart(’ww’,now()) . Week Day datepart(’w',now()) . Quarter of year datepart(’q',now()) . First day of Quarter dateserial(year(now), (datepart(’q',now())-1)*3+1 ,1 ) . Last day of quarter dateserial(year(now), datepart (’q',now() ) *3+1 ,0 ) . First day of week now() – datepart(’w',now())+1 . Last day of week now() + 7- datepart(’w',now())

More about the DatePart and DateSerial functions

Interval Explanation
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Feb
2