Determining a future date...

[NWO]

Hello.

I have a situation where I need to calculate a date out three years from a
given start date. For example, I have a date, say 6/8/2005, and I want to
know what the three year anniversay date is from this date, to inlcude
compensating for leap years. I have played with the date functions and tried
to formulate a formula to solve this, but to no avail. Any ideas would be
helpful.

Thank you.

NWO.

[Peo Sjoblom]

with your date in A1

=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))


--

Regards,

Peo Sjoblom

"NWO" <[email protected]> wrote in message
news:[email protected]...
> Hello.
>
> I have a situation where I need to calculate a date out three years from a
> given start date. For example, I have a date, say 6/8/2005, and I want to
> know what the three year anniversay date is from this date, to inlcude
> compensating for leap years. I have played with the date functions and
tried
> to formulate a formula to solve this, but to no avail. Any ideas would be
> helpful.
>
> Thank you.
>
> NWO.
>
>
>
>
>
>
>

[NWO]

Thank you Peo.

NWO
----------------

"Peo Sjoblom" wrote:

> with your date in A1
>
> =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "NWO" <[email protected]> wrote in message
> news:[email protected]...
> > Hello.
> >
> > I have a situation where I need to calculate a date out three years from a
> > given start date. For example, I have a date, say 6/8/2005, and I want to
> > know what the three year anniversay date is from this date, to inlcude
> > compensating for leap years. I have played with the date functions and
> tried
> > to formulate a formula to solve this, but to no avail. Any ideas would be
> > helpful.
> >
> > Thank you.
> >
> > NWO.
> >
> >
> >
> >
> >
> >
> >
>
>
>

[Sherry Moss]

I need help; I have a date in column B, the formula in Column C is =b5-14, this returns another date.

If that day falls on a Saturday or Sunday, I'd like the returned value to be the previous Friday.

I can't figure out how to do this.

Can you help?

[Bob Phillips]

=B5-14-(WEEKDAY(B5,2)>5)-(WEEKDAY(B5)=1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sherry Moss" <[email protected]>
wrote in message
news:[email protected]...
>
> I need help; I have a date in column B, the formula in Column C is
> =b5-14, this returns another date.
>
> If that day falls on a Saturday or Sunday, I'd like the returned value
> to be the previous Friday.
>
> I can't figure out how to do this.
>
> Can you help?
>
>
> --
> Sherry Moss
> ------------------------------------------------------------------------
> Sherry Moss's Profile:
http://www.excelforum.com/member.php...o&userid=29535
> View this thread: http://www.excelforum.com/showthread...hreadid=492334
>

[Sherry Moss]

Thanks, Bob. This will save me load of time.

[Sherry Moss]

Still need help, I used the suggested formula and it didn't work for me.

Specifically
Column B = Feb 04 06
Column C = b1-14 = Jan 21 06

This result is a Saturday. I want it to give me Friday's date = Jan 20 06.

[Bob Phillips]

Sherry,

Only just spotted this post.

In what way does it not work? It returned 20th Jan for me, which is a
Friday.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sherry Moss" <[email protected]>
wrote in message
news:[email protected]...
>
> Still need help, I used the suggested formula and it didn't work for
> me.
>
> Specifically
> Column B = Feb 04 06
> Column C = b1-14 = Jan 21 06
>
> This result is a Saturday. I want it to give me Friday's date = Jan 20
> 06.
>
>
> --
> Sherry Moss
> ------------------------------------------------------------------------
> Sherry Moss's Profile:
http://www.excelforum.com/member.php...o&userid=29535
> View this thread: http://www.excelforum.com/showthread...hreadid=492334
>

[Sherry Moss]

It returns January 21 for me. Is there anyway I could send you the spreadsheet and you give it a look? I don't know why it would work for you and not me.

[Bob Phillips]

Try again.

Send it to

bob dot phillips at tiscali dot co dot uk

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sherry Moss" <[email protected]>
wrote in message
news:[email protected]...
>
> It returns January 21 for me. Is there anyway I could send you the
> spreadsheet and you give it a look? I don't know why it would work for
> you and not me.
>
>
> --
> Sherry Moss
> ------------------------------------------------------------------------
> Sherry Moss's Profile:
http://www.excelforum.com/member.php...o&userid=29535
> View this thread: http://www.excelforum.com/showthread...hreadid=492334
>

[Bob Phillips]

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sherry Moss" <[email protected]>
wrote in message
news:[email protected]...
>
> It returns January 21 for me. Is there anyway I could send you the
> spreadsheet and you give it a look? I don't know why it would work for
> you and not me.
>
>
> --
> Sherry Moss
> ------------------------------------------------------------------------
> Sherry Moss's Profile:
http://www.excelforum.com/member.php...o&userid=29535
> View this thread: http://www.excelforum.com/showthread...hreadid=492334
>