Microsoft Excel Date Formula?

Vintalage

Does anyone know how to get excel to automatically calculate how many days are left in a month or year? For instance, say I have something that is going to expire in 2 months and I want to know how many days are left a month and a half from now, how do I do that?

Ageek

AFAIK, Excel does not have built-in calendar to know how many days on each month for leap and non-lep years and what years are leap years. To do it precisely you need that data in a table\spreadsheet.

Then you can feed it begin date and end date, take end date and subtract begin date.

The only other old way is to calculate day of year and then convert back into month and day of month after doing:

End date (as Day of Year) -Begin Date (as Day of Year). BUT, this only works when both dates are in same year.

So, to get the number of days still to come in a time frame you start with the process just above. Then, if the result is negative add 365 to it.

Example:

December 15 of one year is today. DOY is 365-16 or 349. You want to know how many days until Jan 20 of next year. Jan 20 is DOY 20.

20-349 is -329, but year increased by one, so real number of days is now -329+365 or 365-329. this is 36. Let's check-- 365-349 is 16 days remaining in year. 20-0 is 20 days into next year. 16
+20 equals 36.

To calc days from now to a date many years in future similar as above, except amount added is 365*number of years. To calc when dates are in aleap year and Feb 29 is in the date range you ad one day to result using non-leap year data to get a subtotal result first.

That is why bills usually are cycled in numbers of days rather than exact day of each month.

John Danielson.