Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
I'm trying to create a sheet that will auto fill in dates based on a user
entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
Question: to help anyone a little better with dates like this than I am...
You say "The following Monday should just take the date from Friday and +1.." "+1"?? Shouldn't Monday be +3 days after a Friday? Or did you mean +1 something else (not days). "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
Say Monday is in A2, Tuesday in B2 etc.
I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
Kassie, this is exactly the formula I've been trying to get working! Thanks
for finding my error in WEEKDAY vs WEEKDAYS. lol Can anyone tell me now, how to leave the day blank if it is a holiday?? I have a list, a range, of dates for that. Lets say Sheet2!, B4:B14. "kassie" wrote: Say Monday is in A2, Tuesday in B2 etc. I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
In fact, maybe I can show what I am trying to do.
Day 1 Monday 15 Jan 07 | Day 2 Tuesday 16 Jan 07 | Day 3 Wednesday 17 Jan 07 Day 6 Monday 22 Jan 07 | Tuesday 23 Jan 07 | Day 7 Wednesday 24 Jan 07| Okay... For monday: A1=Day#, A2="weekday", A3=date) For tuesday: A4=Day#, A5="Weekday",A6=date)...... etc upto friday --Day# increases only when it is a teaching day, not calendar day. --pretending that the second week is a holiday on tuesday, the day# field is blank. --there are no wknds shown on the calendar. "kassie" wrote: Say Monday is in A2, Tuesday in B2 etc. I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
You will have to add an
IF(NOT(ISNA)VLOOKUP(Sheet2!A1,Sheet2!B4:B14,1,FALS E)),"", before Sheet2!$A$1 in each formula, as a final check before accepting the date in Sheet2!A1. Iow if all else is fine, then first check if Vlookup does not find the date in your holiday table. If it finds it, therefore not result in #N/A, leave blank, but if it does not find it, it must return a date. The rest of your formula will have to be tweaked as well, in order to look 2 days back as well. If it finds a date there, but not in the previous day's column, it wil then have to add 2 days iso just 1. If you do not come right, post back with detailed cell info, and I'll write it for you. Looks like you have all the days below each other? Does the 2nd Monday start directly below Friday? -- Hth Kassie Kasselman "christine b" wrote: In fact, maybe I can show what I am trying to do. Day 1 Monday 15 Jan 07 | Day 2 Tuesday 16 Jan 07 | Day 3 Wednesday 17 Jan 07 Day 6 Monday 22 Jan 07 | Tuesday 23 Jan 07 | Day 7 Wednesday 24 Jan 07| Okay... For monday: A1=Day#, A2="weekday", A3=date) For tuesday: A4=Day#, A5="Weekday",A6=date)...... etc upto friday --Day# increases only when it is a teaching day, not calendar day. --pretending that the second week is a holiday on tuesday, the day# field is blank. --there are no wknds shown on the calendar. "kassie" wrote: Say Monday is in A2, Tuesday in B2 etc. I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
Ok, I think you've gone beyond my limited skills with Excel. I do somewhat
understand what your formulas are supposed to do, but I am still not able to get it working. Explaining things would be even more difficult at this point. Is it possible to email the sheet to you? Maybe then you could see it, rather than me trying to explain this calendar/schedule. If not, I thank you very much for your help regardless!! "kassie" wrote: You will have to add an IF(NOT(ISNA)VLOOKUP(Sheet2!A1,Sheet2!B4:B14,1,FALS E)),"", before Sheet2!$A$1 in each formula, as a final check before accepting the date in Sheet2!A1. Iow if all else is fine, then first check if Vlookup does not find the date in your holiday table. If it finds it, therefore not result in #N/A, leave blank, but if it does not find it, it must return a date. The rest of your formula will have to be tweaked as well, in order to look 2 days back as well. If it finds a date there, but not in the previous day's column, it wil then have to add 2 days iso just 1. If you do not come right, post back with detailed cell info, and I'll write it for you. Looks like you have all the days below each other? Does the 2nd Monday start directly below Friday? -- Hth Kassie Kasselman "christine b" wrote: In fact, maybe I can show what I am trying to do. Day 1 Monday 15 Jan 07 | Day 2 Tuesday 16 Jan 07 | Day 3 Wednesday 17 Jan 07 Day 6 Monday 22 Jan 07 | Tuesday 23 Jan 07 | Day 7 Wednesday 24 Jan 07| Okay... For monday: A1=Day#, A2="weekday", A3=date) For tuesday: A4=Day#, A5="Weekday",A6=date)...... etc upto friday --Day# increases only when it is a teaching day, not calendar day. --pretending that the second week is a holiday on tuesday, the day# field is blank. --there are no wknds shown on the calendar. "kassie" wrote: Say Monday is in A2, Tuesday in B2 etc. I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
funtions and conditions
Of course you can! Click on my name to view my profile. Use only lowercase
letters for my email address! -- Hth Kassie Kasselman "christine b" wrote: Ok, I think you've gone beyond my limited skills with Excel. I do somewhat understand what your formulas are supposed to do, but I am still not able to get it working. Explaining things would be even more difficult at this point. Is it possible to email the sheet to you? Maybe then you could see it, rather than me trying to explain this calendar/schedule. If not, I thank you very much for your help regardless!! "kassie" wrote: You will have to add an IF(NOT(ISNA)VLOOKUP(Sheet2!A1,Sheet2!B4:B14,1,FALS E)),"", before Sheet2!$A$1 in each formula, as a final check before accepting the date in Sheet2!A1. Iow if all else is fine, then first check if Vlookup does not find the date in your holiday table. If it finds it, therefore not result in #N/A, leave blank, but if it does not find it, it must return a date. The rest of your formula will have to be tweaked as well, in order to look 2 days back as well. If it finds a date there, but not in the previous day's column, it wil then have to add 2 days iso just 1. If you do not come right, post back with detailed cell info, and I'll write it for you. Looks like you have all the days below each other? Does the 2nd Monday start directly below Friday? -- Hth Kassie Kasselman "christine b" wrote: In fact, maybe I can show what I am trying to do. Day 1 Monday 15 Jan 07 | Day 2 Tuesday 16 Jan 07 | Day 3 Wednesday 17 Jan 07 Day 6 Monday 22 Jan 07 | Tuesday 23 Jan 07 | Day 7 Wednesday 24 Jan 07| Okay... For monday: A1=Day#, A2="weekday", A3=date) For tuesday: A4=Day#, A5="Weekday",A6=date)...... etc upto friday --Day# increases only when it is a teaching day, not calendar day. --pretending that the second week is a holiday on tuesday, the day# field is blank. --there are no wknds shown on the calendar. "kassie" wrote: Say Monday is in A2, Tuesday in B2 etc. I used Sheet2!A1 as the input field In A2 enter : =IF(WEEKDAY(Sheet2!$A$1)<1,"",Sheet2!$A$1) In B2 enter : =IF(A2<"",A2+1,IF(WEEKDAY(Sheet2!$A$1)<2,"",Shee t2!$A$1)) In C,D and E2, increment the <2 by 1, ie 3,4 and 5 The next Monday, enter =IF(E2<"",E2+3,""). For the remainder of the next week, add 1 to the previous day, eg =IF(F2<"",F2+1,""). I have not included holidays though. -- Hth Kassie Kasselman "christine b" wrote: I'm trying to create a sheet that will auto fill in dates based on a user entered start date. the sheet is set up in a calendar fashion (for a course schedule), so the dates need to be filled begining with the start date... ie, start filling on wednesday if the start date entered is in fact a wednesday. The user entry data is on another sheet. Can someone help me build a formula that will do the following, or suggest an easier way??? Monday will: evaluate the startdate, if it is a monday it shows the date, if not it leaves it blank. Tuesday will: evaluate the startdate, if it is tuesday it shows the date, if the start date is monday it shows the date for tuesday (monday + 1), otherwise it stays blank. Wednesday, Thurs, Friday, will do the same as above. The following Monday should just take the date from Friday and +1. Each day of the week after this should also just +1. The catch is also to leave any date that is a holiday is to be left blank (I have a holiday list) so that I can set a condition to format the cells grey. I know its complicated, yet I've seen a simular schedule work this way. Just do not have access to it to use it as a reference. Thanks to anyone who can help. Christine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|