Calculate Date
 

I am trying to create a formula that will take a specific date in a cell and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if the
date falls on a Saturday then give the Friday date. If the date falls on a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

Calculate Date
 

Hi

if you have the analysis toolpak installed (tools / addins / analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a cell
and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if
the
date falls on a Saturday then give the Friday date. If the date falls on
a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

Calculate Date
 

Thanks JulieD,

When filling down the formula I receive "#VALUE!" or "#NUM!" on cells
without irrelavant data. How do I account for this? I tried writing the
formula as =WORKDAY (B6,-35, " ") with no success.

"JulieD" wrote:

Hi

if you have the analysis toolpak installed (tools / addins / analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a cell
and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if
the
date falls on a Saturday then give the Friday date. If the date falls on
a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

Calculate Date
 

Pardon me - irrelavant should be relavant. Cells without relavant data.

"Jerome" wrote:

Thanks JulieD,

When filling down the formula I receive "#VALUE!" or "#NUM!" on cells
without irrelavant data. How do I account for this? I tried writing the
formula as =WORKDAY (B6,-35, " ") with no success.

"JulieD" wrote:

Hi

if you have the analysis toolpak installed (tools / addins / analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a cell
and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if
the
date falls on a Saturday then give the Friday date. If the date falls on
a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

Calculate Date
 

On Thu, 3 Mar 2005 08:43:07 -0800, Jerome
wrote:

I am trying to create a formula that will take a specific date in a cell and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if the
date falls on a Saturday then give the Friday date. If the date falls on a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

If I understand you correctly, you first want to subtract 35 days from the
particular date, and then change it only if that date falls on a weekend.

The function "=A2-32" will give you a date 32 days prior, not 35.

For a day 35 days prior, altered as you instruct if the day falls on a weekend,
use:

=A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1)

If, on the other hand, you are looking for 35 working days prior to the date in
A2, so that no weekends are even counted, then look at the WORKDAY function.


--ron

Calculate Date
 

I want to be able to copy the formula (fill down) to the end of the column.
There are spaces and column headers in between valid data. How do I prevent
the "#VALUE!" and "#NUM!" from showing up?

"Ron Rosenfeld" wrote:

On Thu, 3 Mar 2005 08:43:07 -0800, Jerome
wrote:

I am trying to create a formula that will take a specific date in a cell and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if the
date falls on a Saturday then give the Friday date. If the date falls on a
Sunday, then give the Monday date. I am not sure where to begin. I wrote
the following function of "= A2 - 32" Any help is greatly appreciated.

If I understand you correctly, you first want to subtract 35 days from the
particular date, and then change it only if that date falls on a weekend.

The function "=A2-32" will give you a date 32 days prior, not 35.

For a day 35 days prior, altered as you instruct if the day falls on a weekend,
use:

=A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1)

If, on the other hand, you are looking for 35 working days prior to the date in
A2, so that no weekends are even counted, then look at the WORKDAY function.


--ron

Calculate Date
 

Hi Jerome

nest it if an IF statement e.g.
=IF(B6<"",WORKDAY(B6,-35),"")
or whatever test you want to use

Cheers
JulieD

"Jerome" wrote in message
...
Pardon me - irrelavant should be relavant. Cells without relavant data.

"Jerome" wrote:

Thanks JulieD,

When filling down the formula I receive "#VALUE!" or "#NUM!" on cells
without irrelavant data. How do I account for this? I tried writing the
formula as =WORKDAY (B6,-35, " ") with no success.

"JulieD" wrote:

Hi

if you have the analysis toolpak installed (tools / addins / analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a
cell
and
give the date 35 days prior. I am only concerned with Mon-Fri. So,
if
the
date falls on a Saturday then give the Friday date. If the date
falls on
a
Sunday, then give the Monday date. I am not sure where to begin. I
wrote
the following function of "= A2 - 32" Any help is greatly
appreciated.

Calculate Date
 

On Thu, 3 Mar 2005 13:55:03 -0800, Jerome
wrote:

I want to be able to copy the formula (fill down) to the end of the column.
There are spaces and column headers in between valid data. How do I prevent
the "#VALUE!" and "#NUM!" from showing up?

Two methods, assuming the data starts in A2 and the formula is in B2:

1. Embed your formula in an IF statement:

=IF(A2="","",A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1))

2. Use conditional formatting to format the cell with a font the same color as
the background color (e.g. white) if there is an error:

Format/Conditional Formatting
Formula Is: =ISERROR(B2)


--ron

Calculate Date
 

Thank you Julie that worked.

"JulieD" wrote:

Hi Jerome

nest it if an IF statement e.g.
=IF(B6<"",WORKDAY(B6,-35),"")
or whatever test you want to use

Cheers
JulieD

"Jerome" wrote in message
...
Pardon me - irrelavant should be relavant. Cells without relavant data.

"Jerome" wrote:

Thanks JulieD,

When filling down the formula I receive "#VALUE!" or "#NUM!" on cells
without irrelavant data. How do I account for this? I tried writing the
formula as =WORKDAY (B6,-35, " ") with no success.

"JulieD" wrote:

Hi

if you have the analysis toolpak installed (tools / addins / analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a
cell
and
give the date 35 days prior. I am only concerned with Mon-Fri. So,
if
the
date falls on a Saturday then give the Friday date. If the date
falls on
a
Sunday, then give the Monday date. I am not sure where to begin. I
wrote
the following function of "= A2 - 32" Any help is greatly
appreciated.

Calculate Date
 

Thank you Ron, this method works also.

"Ron Rosenfeld" wrote:

On Thu, 3 Mar 2005 13:55:03 -0800, Jerome
wrote:

I want to be able to copy the formula (fill down) to the end of the column.
There are spaces and column headers in between valid data. How do I prevent
the "#VALUE!" and "#NUM!" from showing up?

Two methods, assuming the data starts in A2 and the formula is in B2:

1. Embed your formula in an IF statement:

=IF(A2="","",A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1))

2. Use conditional formatting to format the cell with a font the same color as
the background color (e.g. white) if there is an error:

Format/Conditional Formatting
Formula Is: =ISERROR(B2)


--ron

Calculate Date
 

you're welcome and thanks for the feedback

"Jerome" wrote in message
...
Thank you Julie that worked.

"JulieD" wrote:

Hi Jerome

nest it if an IF statement e.g.
=IF(B6<"",WORKDAY(B6,-35),"")
or whatever test you want to use

Cheers
JulieD

"Jerome" wrote in message
...
Pardon me - irrelavant should be relavant. Cells without relavant
data.

"Jerome" wrote:

Thanks JulieD,

When filling down the formula I receive "#VALUE!" or "#NUM!" on cells
without irrelavant data. How do I account for this? I tried writing
the
formula as =WORKDAY (B6,-35, " ") with no success.

"JulieD" wrote:

Hi

if you have the analysis toolpak installed (tools / addins /
analysis
toolpak) you can use the WORKDAY function
e.g.
=WORKDAY(A2,-35)

Cheers
JulieD

"Jerome" wrote in message
...
I am trying to create a formula that will take a specific date in a
cell
and
give the date 35 days prior. I am only concerned with Mon-Fri.
So,
if
the
date falls on a Saturday then give the Friday date. If the date
falls on
a
Sunday, then give the Monday date. I am not sure where to begin.
I
wrote
the following function of "= A2 - 32" Any help is greatly
appreciated.