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

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

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

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

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

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

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

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