Calculate date range (Excel 2000)

Topic

New Reply

How do I calculate date ranges? (The “help” answers were not very clear.) For example: I have the date (10/21/03) entered and I want the next column to show a date 7 years from that date (10/21/10). What formula should I use?

Reply | Quote

Replies

If your date is in A1 you can use the formula:

=DATE(YEAR(A1)+7,MONTH(A1), DAY(A1))

Steve

Reply | Quote

If your date is in A1 you can use the formula:

=DATE(YEAR(A1)+7,MONTH(A1), DAY(A1))

Steve

Reply | Quote

Say that you have a date in cell A1.

To get the date 7 days later: =A1+7 or =DATE(YEAR(A1),MONTH(A1),DAY(A1)+7)

To get the date 7 months later: =DATE(YEAR(A1),MONTH(A1)+7,DAY(A1))

To get the date 7 years later: =DATE(YEAR(A1)+7,MONTH(A1),DAY(A1))

Excel automatically adjusts for “overflow” in the day or month.

Reply | Quote

Say that you have a date in cell A1.

To get the date 7 days later: =A1+7 or =DATE(YEAR(A1),MONTH(A1),DAY(A1)+7)

To get the date 7 months later: =DATE(YEAR(A1),MONTH(A1)+7,DAY(A1))

To get the date 7 years later: =DATE(YEAR(A1)+7,MONTH(A1),DAY(A1))

Excel automatically adjusts for “overflow” in the day or month.

Reply | Quote

Two of my favorite functions that were included in Excel 97’s Analysis Toolpak are EDATE and EOMONTH. These may be part of Excel 2000’s core set of functions. =EDATE(date,7) would give you the same date 7 months later. =EOMONTH(date,7) would move you ahead seven months and give you the last day of that month. EOMONTH is particularly useful for setting up a series of month end dates.

Reply | Quote

EDATE and EOMONTH are still part of the Analysis Toolpak in Excel 2002.

There is no equivalent EYEAR function. To get the date seven years from the date in A1 (as in the original question in this thread), one can use =EDATE(A1,7*12)

Reply | Quote

EDATE and EOMONTH are still part of the Analysis Toolpak in Excel 2002.

There is no equivalent EYEAR function. To get the date seven years from the date in A1 (as in the original question in this thread), one can use =EDATE(A1,7*12)

Reply | Quote

Two of my favorite functions that were included in Excel 97’s Analysis Toolpak are EDATE and EOMONTH. These may be part of Excel 2000’s core set of functions. =EDATE(date,7) would give you the same date 7 months later. =EOMONTH(date,7) would move you ahead seven months and give you the last day of that month. EOMONTH is particularly useful for setting up a series of month end dates.

Reply | Quote