Days to Years ? How

[Corey]

Currently i have an induction register that has 3 worksheets.

sheet1 = Date the induction was carried out

sheet2 = Frequency that the induction is current for(At moment i have
365.25 as 1 Year)

sheet3 = Display of dates due with colour coded shading according to
when the induction is due.


Is there a a way i can in SHEET2 place 1,2,3,5,10 for YEARS rather than
365.25 for 1 year etc?


Regards

Corey

 

[Arvi Laanemets]

Hi

To get a date n years late to date in Sheet1A1
=DATE(YEAR(Sheet1!A1)+n,MONTH(Sheet1!A1),DAY(Sheet1!A1)
, or taking into account the possibility the start date being 29 February of
leap year
=MIN(DATE(YEAR(Sheet1!A1)+n,MONTH(Sheet1!A1)+1,0),DATE(YEAR(Sheet1!A1)+n,MONTH(Sheet1!A1),DAY(Sheet1!A1))

To get a date n months late to date in Sheet1A1
=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+n,DAY(Sheet1!A1)
, or taking into account different length of months
=MIN(DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+n+1,0),DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+n,DAY(Sheet1!A1))

 

[Corey]

Thanks for the reply,
But i am not after a conversion for year to date.
But i am after a way to enter say:
(1) to represent 1 year instead of (365)
(2) to represent 2 years instead of (730)

etc.
Regards

Corey

 

[Arvi Laanemets]

???

On sheet 2 you simply enter the frequency as number of years (or months).
Formulas are meant to use on sheet 3 - to calculate due dates, where n is
calculated based on frequency from sheet 2 and on number of particular
conversion due date on sheet 3. P.e. when you have frequency=1, and you want
to calculate 2nd due date for this conversion, then n=2*frequency. How do
you get both frequency and due date number for a conversion into formula,
depends on your data setup - I did give you the general formula.