Date calculation examples

 2000-04-14    Dates    0    45

The table below shows some useful example formulas for date calculations. The examples assumes that cell A1 contains a valid date, and uses a semicolon (;) as list separator, replace with comma (,) if necessary.

Description Function/Formula
Today's date =TODAY()
A date 14 days from today =TODAY()+14
Return the current day 
1=Monday, 7=Sunday
=WEEKDAY(TODAY();2)
Return the current day =DAY(TODAY())
Return the current month =MONTH(TODAY())
Return the current year  =YEAR(TODAY())
The date for Monday in the previous week =TODAY()-WEEKDAY(TODAY();2)-6
The date for Monday in the current week =TODAY()-WEEKDAY(TODAY();2)+1
The date for Monday in the next week =TODAY()-WEEKDAY(TODAY();2)+8
Create a valid date =DATE(2002;12;24)
Create a valid date =DATEVALUE("1.1.1980")
Count of days in a month =DAY(DATE(YEAR(A1);MONTH(A1)+1;0))
Last date in a month =DATE(YEAR(A1);MONTH(A1)+1;0)
Determine which quarter a date belongs to =CHOOSE(MONTH(A1);1;1;1;2;2;2;3;3;3;4;4;4)


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.