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

Comments are closed.