account for weekends in date formula
 

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

Try this:

=IF(OR($A5="",Q5=""),"",N5+LOOKUP(WEEKDAY(Q5),{1,2 ,3,4,5,6,7},{1,1,1,1,1,3,2}))

Biff

"slinger" wrote in message
...
I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a
Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls
on a
Saturday to add 2 (again so that the formula returns a date for Monday.)
The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

Have you considered the WORKDAY function?
It returns the date that is n-workdays from a base date.

(Note: the WORKDAY function is part of the Analysis ToolPak add-in)

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",WORKDAY(N5,1))

Alternatively, maybe this
=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+CHOOSE(WEEK DAY(Q5,2),1,1,1,1,3,2,1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"slinger" wrote:

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

Don't know my own formula, Q5 isn't a date (oops), I changed the formula a
bit to correct that, but I get an error that says "Inconsistent Formula." It
seems to be working though.

What I have now is

=IF(OR($A5="",Q5=""),"",N5+LOOKUP(WEEKDAY(N5),{1,2 ,3,4,5,6,7},{1,1,1,1,1,3,2}))

$A5 is a date used for calculation, Q5 is from a dropdown list, N5 is a date
based off of $A5, jsut to clarify




"Biff" wrote:

Try this:

=IF(OR($A5="",Q5=""),"",N5+LOOKUP(WEEKDAY(Q5),{1,2 ,3,4,5,6,7},{1,1,1,1,1,3,2}))

Biff

"slinger" wrote in message
...
I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a
Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls
on a
Saturday to add 2 (again so that the formula returns a date for Monday.)
The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

Unfortunately I am unable to use the "workday" function as I cannot determine
who else in the company has the add-in enabled. and the other, (Once I
corrected my error) still gives me an "inconsistent formula" error

"Ron Coderre" wrote:

Have you considered the WORKDAY function?
It returns the date that is n-workdays from a base date.

(Note: the WORKDAY function is part of the Analysis ToolPak add-in)

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",WORKDAY(N5,1))

Alternatively, maybe this
=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+CHOOSE(WEEK DAY(Q5,2),1,1,1,1,3,2,1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"slinger" wrote:

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

Hi...slinger...your formula looks good for me...
Inconsistent formula doesnt mean your formula is wrong !!! Its just a type
of notice regarding comparison of other formulas adjacent to it..!!!
Anyway...based on the lookup
when N5 is on Friday,Sat or Sun then your result is Monday...
when N5 is on Monday then your result is Tuesday....
then when N5 is Thursday then your result will be FRIDAY...
is this an appointment scheduling...getting rid of friday/sat/sun?

"slinger" wrote:

Unfortunately I am unable to use the "workday" function as I cannot determine
who else in the company has the add-in enabled. and the other, (Once I
corrected my error) still gives me an "inconsistent formula" error

"Ron Coderre" wrote:

Have you considered the WORKDAY function?
It returns the date that is n-workdays from a base date.

(Note: the WORKDAY function is part of the Analysis ToolPak add-in)

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",WORKDAY(N5,1))

Alternatively, maybe this
=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+CHOOSE(WEEK DAY(Q5,2),1,1,1,1,3,2,1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"slinger" wrote:

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

I get an error that says "Inconsistent Formula." It
seems to be working though.

That's just Excel "trying to be helpful". It means you may have formulas in
neighboring cells that are different. If you don't like these "helpful
hints" from Excel you can turn them off. Goto ToolsOptionsError Checking
tab. Under RULES, you'll see a bunch of things Excel "warns" you about. You
can uncheck the ones you don't want to be bothered with. Personally, I have
them all unchecked.

Biff

"slinger" wrote in message
...
Don't know my own formula, Q5 isn't a date (oops), I changed the formula a
bit to correct that, but I get an error that says "Inconsistent Formula."
It
seems to be working though.

What I have now is

=IF(OR($A5="",Q5=""),"",N5+LOOKUP(WEEKDAY(N5),{1,2 ,3,4,5,6,7},{1,1,1,1,1,3,2}))

$A5 is a date used for calculation, Q5 is from a dropdown list, N5 is a
date
based off of $A5, jsut to clarify




"Biff" wrote:

Try this:

=IF(OR($A5="",Q5=""),"",N5+LOOKUP(WEEKDAY(Q5),{1,2 ,3,4,5,6,7},{1,1,1,1,1,3,2}))

Biff

"slinger" wrote in message
...
I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a
Friday
to add 3 (so that the formula returns a date for monday, and if Q5
falls
on a
Saturday to add 2 (again so that the formula returns a date for
Monday.)
The
remainder of the formula needs to stay intact however, as there are
other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

appointment scheduling, sort of, it doesn't get rid of friday.

Basically it is a log following a document and who is supposed to have it
when. They don't work on the weekends so instead of showing that someone is
supposed to have the document on Saturday, it corrects to Monday, then other
columns that have the same formula will automatically adjust also. The whole
formula is based on a start date $A5, and checks two columns to see if any
information is required for this column. (Very complicated to explain without
showing an example.) and I have tested the formula suggeste and it's still
not quite right. Since I will not be the only person viewing the spreadsheet
I would like to make sure that there is nothing that will be called in to
quesion by the others viewing it, which is my desire to not have any formula
errors or inconsistencies, etc.

"miner" wrote:

Hi...slinger...your formula looks good for me...
Inconsistent formula doesnt mean your formula is wrong !!! Its just a type
of notice regarding comparison of other formulas adjacent to it..!!!
Anyway...based on the lookup
when N5 is on Friday,Sat or Sun then your result is Monday...
when N5 is on Monday then your result is Tuesday....
then when N5 is Thursday then your result will be FRIDAY...
is this an appointment scheduling...getting rid of friday/sat/sun?

"slinger" wrote:

Unfortunately I am unable to use the "workday" function as I cannot determine
who else in the company has the add-in enabled. and the other, (Once I
corrected my error) still gives me an "inconsistent formula" error

"Ron Coderre" wrote:

Have you considered the WORKDAY function?
It returns the date that is n-workdays from a base date.

(Note: the WORKDAY function is part of the Analysis ToolPak add-in)

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",WORKDAY(N5,1))

Alternatively, maybe this
=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+CHOOSE(WEEK DAY(Q5,2),1,1,1,1,3,2,1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"slinger" wrote:

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.

account for weekends in date formula
 

slinger....try ToolsProtectionProtect Sheet "your password"

save/close and open again
see if u can view or read the error bars...This sheet is only for viewing as
you said for others.

"slinger" wrote:

appointment scheduling, sort of, it doesn't get rid of friday.

Basically it is a log following a document and who is supposed to have it
when. They don't work on the weekends so instead of showing that someone is
supposed to have the document on Saturday, it corrects to Monday, then other
columns that have the same formula will automatically adjust also. The whole
formula is based on a start date $A5, and checks two columns to see if any
information is required for this column. (Very complicated to explain without
showing an example.) and I have tested the formula suggeste and it's still
not quite right. Since I will not be the only person viewing the spreadsheet
I would like to make sure that there is nothing that will be called in to
quesion by the others viewing it, which is my desire to not have any formula
errors or inconsistencies, etc.

"miner" wrote:

Hi...slinger...your formula looks good for me...
Inconsistent formula doesnt mean your formula is wrong !!! Its just a type
of notice regarding comparison of other formulas adjacent to it..!!!
Anyway...based on the lookup
when N5 is on Friday,Sat or Sun then your result is Monday...
when N5 is on Monday then your result is Tuesday....
then when N5 is Thursday then your result will be FRIDAY...
is this an appointment scheduling...getting rid of friday/sat/sun?

"slinger" wrote:

Unfortunately I am unable to use the "workday" function as I cannot determine
who else in the company has the add-in enabled. and the other, (Once I
corrected my error) still gives me an "inconsistent formula" error

"Ron Coderre" wrote:

Have you considered the WORKDAY function?
It returns the date that is n-workdays from a base date.

(Note: the WORKDAY function is part of the Analysis ToolPak add-in)

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",WORKDAY(N5,1))

Alternatively, maybe this
=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+CHOOSE(WEEK DAY(Q5,2),1,1,1,1,3,2,1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"slinger" wrote:

I have the following formula set in one of my columns

=IF(OR(ISBLANK($A5),ISBLANK(Q5)),"",N5+1)

I need this to account for weekend days however and if Q5 falls on a Friday
to add 3 (so that the formula returns a date for monday, and if Q5 falls on a
Saturday to add 2 (again so that the formula returns a date for Monday.) The
remainder of the formula needs to stay intact however, as there are other
functions that depend on it.

Any help would be greatly appreciated.

Thanks.