auto-calculations using calendar and dates (Excel-Office 2000)
 

I want to have excel calculate the last 2 columns but need help with the
formulas. My spreadsheet should show a summary of part numbers. Each part
has a service life which begins on the date of installation. I am hoping
that excel can calculate "Date to be removed" and "days to go" automatically
with insertion of a proper formula.
service life date installed date to be removed
days to go
12 months 10/26/2004 10/26/2005
280

I would appreciate help with the formula for the last two columns?

Sally;
Assuming the following:
Service Life is always entered as a number of months
Col A is Service Life
Col B is DateInstalled
Col C is Date2BRemoved
Col D is Days2Go
Use the following formula in Col C:

=A2+(B2*(365.25/12))

Use the following formula in Col D:

=YEARFRAC(D2,TODAY())*365.25

This doesn't take into account that Col A might contain text (ie: 12 months)
rather than just a numeric value. Nor does it take into account the real
possibility that Col A might also be stated as a value of days or years (ie:
300 days, or 2 years). But it should give you something to work with.

Dan Knight


"Sally from Chatham" wrote:

I want to have excel calculate the last 2 columns but need help with the
formulas. My spreadsheet should show a summary of part numbers. Each part
has a service life which begins on the date of installation. I am hoping
that excel can calculate "Date to be removed" and "days to go" automatically
with insertion of a proper formula.
service life date installed date to be removed
days to go
12 months 10/26/2004 10/26/2005
280

I would appreciate help with the formula for the last two columns?

If, as you say, service life is entered in column A as number of months,
perhaps a better formula for the date-to-be-removed, in column C, is

=DATE(YEAR(B2),MONTH(B2)+A2,DAY(B2))

and the number of days remaining between the current date and the date to be
removed in column C is =C2-TODAY(). If the latter is wanted in Months and
days, you can use

=DATEDIF(TODAY(),C2,"m")&" months, "&DATEDIF(TODAY(),C2,"ym")&" days"


On Thu, 17 Feb 2005 15:47:02 -0800, "Dan Knight"
wrote:

Sally;
Assuming the following:
Service Life is always entered as a number of months
Col A is Service Life
Col B is DateInstalled
Col C is Date2BRemoved
Col D is Days2Go
Use the following formula in Col C:

=A2+(B2*(365.25/12))

Use the following formula in Col D:

=YEARFRAC(D2,TODAY())*365.25

This doesn't take into account that Col A might contain text (ie: 12 months)
rather than just a numeric value. Nor does it take into account the real
possibility that Col A might also be stated as a value of days or years (ie:
300 days, or 2 years). But it should give you something to work with.

Dan Knight


"Sally from Chatham" wrote:

I want to have excel calculate the last 2 columns but need help with the
formulas. My spreadsheet should show a summary of part numbers. Each part
has a service life which begins on the date of installation. I am hoping
that excel can calculate "Date to be removed" and "days to go"
automatically
with insertion of a proper formula.
service life date installed date to be removed
days to go
12 months 10/26/2004 10/26/2005
280

I would appreciate help with the formula for the last two columns?