Tom’s Tutorials For Excel: More Date Formulas

Tom’s Tutorials For Excel: More Date Formulas

Following up on yesterday’s theme, here are more common scenarios involving date calculations. Referring to the picture…

…The formula in cell B3 that is copied across to cell G3 for last day of month, first day of next month, and so on, is
=DATE(YEAR(A3),MONTH(A3)+1,IF(DAY(A3)=1,0,1))

…The formula in cell F6 that evaulates a past, present, or future date depending on today’s date is
=IF(E6 < TODAY(),"Past",IF(E6 > TODAY(),"Future","Today"))

…The formula in cell A9 that is copied down to cell A17 for filling “double dates” is
=IF(MOD(ROW(),2)=1,A8,A8+1)

…The formula in cell G9 that sums numbers in one column depending on the month and year of another column is
=SUMPRODUCT((TEXT($E$9:$E$17,"yyyymm")="201109")*($F$9:$F$17))

…The formula in cell B20 that returns just the date from the NOW function is
=TRUNC(A20)

…The formula in cell C20 that returns just the time from the NOW function is
=MOD(A20,1)

…The formula in cell G20 that counts how many dates in a range are older than today is
=COUNTIF(E9:E17," < " & TODAY())