Date calculation examples

The table below shows some useful example formulas for date calculations. The examples assumes that cell A1 contains a valid date.

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)

Document last updated 2000-04-14 12:44:38 Printerfriendly version

User comments:

shiv from India pune wrote (2005-07-28 06:00:24 CET):

date calculation help req
can u help me for find out time spend between period written on two cells with effect of holidays, day, hours,minutes

Ole P. from Norway wrote (2004-04-18 21:26:06 CET):

Re: Contenido de la pagina
Since I'm not fluent in Spanish, these pages will not be translated into Spanish :-)

jhonny from Colombia wrote (2004-04-18 03:34:04 CET):

Contenido de la pagina
hola
seria muy bueno tener esta pagia tambien en español