Date calculation examples
2000-04-14 Dates 0 205
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) |