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
--
HTH

JonR


"Toby0320" wrote:

> 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
>
>

[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

[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" wrote:

> 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
>
>

[Toby0320]

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

Thanks though!

JonR wrote:
> Check out Chip Pearson's page for tips on working with dates and times
>
> http://www.cpearson.com/excel/datetime.htm#AddingDates
> --
> HTH
>
> JonR
>
>
> "Toby0320" wrote:
>
> > 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
> >
> >

[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

[email protected]
[email protected] with @tiscali.co.uk


"Toby0320" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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 wrote:
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=559004

[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

Steve

[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 wrote:
> Toby,
>
> This should do it for you.
>
> =IF(OR(ISBLANK(C2),EDATE(C2,12)<EDATE(B2,36)),EDATE(B2,36),EDATE(C2,12))
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=559004

[SteveG]

Denise,

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

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

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



HTH


Steve

[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 wrote:
> Denise,
>
> In your OP you stated that if there was no termination date the
> retention date was 3 years out. Now you want to remove that or is
> there some condition that needs to be met in order not to show the
> retention date?
>
> If you always want to return a blank if there is no termination date
> then,
>
> =IF(ISBLANK(C2),"",IF(EDATE(C2,12)<EDATE(B2,36),EDATE(B2,36),EDATE(C2,12)))
>
>
>
> HTH
>
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=559004

[SteveG]

Denise,

You're welcome and thanks for the feedback.

Cheers,

Steve