XL: Calculating Days Remaining in a Time Period

ID: Q81026

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

In Microsoft Excel, to find the number of days remaining in a time period (even division of a year) containing a specific date, use the following formula:


   =DATE(YEAR(date),-INT(-(MONTH(date))/period)*period+1,1)-1-date

Replace "date" with the actual date, and replace "period" with the number of months in a period (for example, to calculate on a quarterly basis, use 3 as the period). This formula works for even divisions of a year and does not allow for variations in starting and ending dates of periods.

For example, using 8/4/92 as the date and 3 as the period would yield the value 57, meaning that there are 57 days remaining from 8/4/92 till the end of the quarter (3-month period) that contains the date.

NOTE: The value used for period should be an even divisor of 12 (that is, 2, 3, 4 or 6).

REFERENCES

"Microsoft Excel Function Reference," for the Macintosh, version 3.0, page 44

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2 2.20 3.0

Keywords :
Version :
Platform :
Issue type :