ERLANDSEN DATA CONSULTING Excel & VBA Tips

# 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