Tom’s Tutorials For Excel: Date Formulas

Tom’s Tutorials For Excel: Date Formulas

Here are a few date formulas for common situations. Referring to the picture…

…The formula for text and date in cell C1 is
="Today's date is "&A2
The better formula in cell C2 is
="Today's date is "&TEXT(A2,"MMM DD, YYYY")

…The formula in cell B5 for wrapping date and time in the same cell is
=TEXT(TODAY(),"dd/mm/yy")&CHAR(10)&TEXT(NOW(),"hh:mm AM/PM")

…The formula in cell C7 for the last date of the previous month is
=DATE(YEAR(A2),MONTH(A2),0)

…The formula in cell B10 for adding a month to a date is
=MIN(DATE(YEAR(A2),MONTH(A2)+{2,1},DAY(A2)*{0,1}))

…The formula in cell B13 to determine a person’s age is
=DATEDIF(A13,TODAY(),"Y")&" years, "&DATEDIF(A13,TODAY(),"YM")&" months,
"&DATEDIF(A13,TODAY(),"MD")&" days"
Note, I set this formula into two lines on this web page so all browsers can read it, but just like any formula, it is entered on a single line.

…The formula in cell C18 copied to C19 for determining a “within range” date is
=IF(AND($B$16 < =B18,$B$17 > =B18),
"OK -- Within Start and End",DATEDIF($B$16,B18,"M") & " month(s) difference")
Note, I set this formula into two lines on this web page so all browsers can read it, but just like any formula, it is entered on a single line.