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