Calculate Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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.
Click to expand...

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
 
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?
 
Hi Jerome

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

Cheers
JulieD
 
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?
Click to expand...

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
 
Thank you Julie that worked.

JulieD said:
Hi Jerome

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

Cheers
JulieD
Click to expand...
 
Thank you Ron, this method works also.

Ron Rosenfeld said:
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
Click to expand...
 
Back
Top