Calculating dates

[Toby0320]

Hi everyone I am new here and hoping somebody can help me. I am trying
to create a formula which will allow me to compare dates and determine
when I can store paperwork offsite. For example:

If a client has been with us for 3 years from start date (they will all
have different start dates) then it is okay for me to store their
paperwork offsite. However if a client hires us and terminates us
before the 3 year period is up then we only have to wait one year after
the date they terminated us to get rid of the paperwork. I have three
columns of data now - One is the client name, second is the start date,
and third is the terminated date if applicable. I would like the
fourth column to show the date each client's paperwork may be stored.
Is this possible?

Thank you in advance for any help anyone can provide!

Toby

 

[JonR]

Check out Chip Pearson's page for tips on working with dates and times

http://www.cpearson.com/excel/datetime.htm#AddingDates

 

[Elkar]

Try this:

=IF(C2<>"",DATE(YEAR(C2)+1,MONTH(C2),DAY(C2)),DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)))

But what if the client terminates during the 3rd year? Do you still store
offsite 3 years after start date, even if that is sooner than 1 year after
termination? If so, then try this formula:

=IF(C2<>"",MIN(DATE(YEAR(C2)+1,MONTH(C2),DAY(C2)),DATE(YEAR(B2)+3,MONTH(B2),DAY(B2))),DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)))

HTH,
Elkar

 

[Toby0320]

Thanks JonR, I did check out that page but I am still having trouble
coming up with the correct formula.

Thanks though!

 

[SteveG]

Toby,

Try,

=IF(ISBLANK(C2),EDATE(B2,36),EDATE(C2,12))

Where C2 is the Term Date, B2 the Start Date.

The EDATE function requires the Analysis Toolpak to be installed. Go
to Tools, Add-Ins, Select the Analysis Toolpak option and click OK.


HTH

Steve

 

[Sandy Mann]

Toby,

With the Names in Column A, Start Dates in Column B, Termination dates, if
any in Column C try in Column D:

=IF(AND(A2="",B2=""),"",IF(C2="",DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)),DATE(YEAR(C2)+1,MONTH(C2),DAY(C2))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Toby0320]

Steve,

Thanks for your help this will work perfectly. Question though - I
don't think I specified this in my previous post - The criteria for the
formula is 3 years after client hires us or 1 year after they fire us,
whichever is later. Is there a way to adjust the formula for this?

Thanks!

 

[SteveG]

Toby,

This should do it for you.

=IF(OR(ISBLANK(C2),EDATE(C2,12)<EDATE(B2,36)),EDATE(B2,36),EDATE(C2,12))

HTH

Stev

 

[Toby0320]

Big help - thanks. If there is no term date though can we get it not
calculate the 3 year date from hire?

Denise

 

[SteveG]

Denise,

In your OP you stated that if there was no termination date th
retention date was 3 years out. Now you want to remove that or i
there some condition that needs to be met in order not to show th
retention date?

If you always want to return a blank if there is no termination dat
then,

=IF(ISBLANK(C2),"",IF(EDATE(C2,12)<EDATE(B2,36),EDATE(B2,36),EDATE(C2,12)))



HTH


Stev

 

[Toby0320]

This is perfect - Thank you so much for your help and your patience
since I was not very clear (I was getting conflicting information).

Thanks!

 

[SteveG]

Denise,

You're welcome and thanks for the feedback.

Cheers,

Stev