Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formulas | Excel Discussion (Misc queries) | |||
date formula | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
Trending Formula Results by Date | Excel Worksheet Functions | |||
date that updates itself daily, plus another formula please?? | Excel Worksheet Functions |