Calculating dates
 

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

Calculating dates
 

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

Calculating dates
 

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

Calculating dates
 

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

Calculating dates
 

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

Calculating dates
 

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,M ONTH(B2),DAY(B2)),DATE(YEAR(C2)+1,MONTH(C2),DAY(C2 ))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Toby0320" wrote in message
oups.com...
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

Calculating dates
 

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

Calculating dates
 

Toby,

This should do it for you.

=IF(OR(ISBLANK(C2),EDATE(C2,12)<EDATE(B2,36)),EDAT E(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

Calculating dates
 

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)),EDAT E(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

Calculating dates
 

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),ED ATE(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

Calculating dates
 

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),ED ATE(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

Calculating dates
 

Denise,

You're welcome and thanks for the feedback.

Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=559004