I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

It sure would make it easier if you gave us some details with which to work.
We could make all kinds of guesses about what works and what doesn't, but
they would only be guesses


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

1) what do you *think* this formula does, and what cell is it in?
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

2) what is in cells Q8, F16, and G16?
3) how do those cells relate to the formula above?
4) most important - in plain English, what are you trying to accomplish?
Maybe your approach is convoluted and the people here could help you
streamline the approach - but we need to have an idea of your objective, and
that doesn't come across in either of your posts


"hellsbells" wrote:

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

Hi I posted this just before you responded - does this make any sense to you?



I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

Hi

1. the formula refers back to a cell which we put an initial project start
date into.
2. Q8 refers to the month (including the amount of days in it). F16 is the
task start date and G16 is the task end date.
3. These cells are to show on the spreadsheet the duration of the task and
how long the overall task takes.
4. i am trying to show the overall duration of the project i am working on
and how long each of the tasks within these last. basically its the sort of
thing that would normally be done in project but i dont have it unfortunately
so i have to try and produce this on excel.

i hope this helps and thanks for your help

"Duke Carey" wrote:

1) what do you *think* this formula does, and what cell is it in?
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

2) what is in cells Q8, F16, and G16?
3) how do those cells relate to the formula above?
4) most important - in plain English, what are you trying to accomplish?
Maybe your approach is convoluted and the people here could help you
streamline the approach - but we need to have an idea of your objective, and
that doesn't come across in either of your posts


"hellsbells" wrote:

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

No it doesn't make sense. You need to be specific. See Duke Carey's
response for the type of information you need to provide us.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

Hi I posted this just before you responded - does this make any sense to you?



I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

OK Helen, let's try this again.

What do you THINK this formula does?

=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

It calculates a date based on another date, but what does this supposed
calculation signify? In other words, what date are you trying to calculate?
How does that calculated date interact with any of Q8, F16, and G16?

Next, how does the 'month (including the amount of days in it)' get into Q8?

How do the task start and end dates get into F16 and G16?

C'mon - we're trying to help you. We can't see your worksheet and the info
you have provided so far gives us NO CHANCE to decipher your problem.





"hellsbells" wrote:

Hi

1. the formula refers back to a cell which we put an initial project start
date into.
2. Q8 refers to the month (including the amount of days in it). F16 is the
task start date and G16 is the task end date.
3. These cells are to show on the spreadsheet the duration of the task and
how long the overall task takes.
4. i am trying to show the overall duration of the project i am working on
and how long each of the tasks within these last. basically its the sort of
thing that would normally be done in project but i dont have it unfortunately
so i have to try and produce this on excel.

i hope this helps and thanks for your help

"Duke Carey" wrote:

1) what do you *think* this formula does, and what cell is it in?
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

2) what is in cells Q8, F16, and G16?
3) how do those cells relate to the formula above?
4) most important - in plain English, what are you trying to accomplish?
Maybe your approach is convoluted and the people here could help you
streamline the approach - but we need to have an idea of your objective, and
that doesn't come across in either of your posts


"hellsbells" wrote:

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

Hi Duke

Sorry I was on a training course yesterday so never got this until this
morning.

The date relates to the project start date which we input into cell E3. if
we change the project start date in E3 then it should (in theory) update all
the other dates in the spreadsheet (excluding the task dates which are
inputted manually (F16 +G16))
Cell Q8 relates to the cell I7 which holds
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+
(2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1)))) in it. This cell is then linked to
the cells below it and then in theory should change colour if the dates are
within the field. Once we input the information into cell F16 and G16 then
cells I16 to s16 should be highlighted to show that the formula is true.
However the cells are only being highlighted for dates that are full months
not partial months. i.e. 01 march 2007 - 15 june 2007 only march to may are
highlighted not june.

I tried pasting a copy of the spreadsheet to this so you could see it but it
wouldnt allow it im afraid. I know im not explaining this clearly to you and
i do apologise. I never created this spreadsheet - only inherited it and the
person who did make it is long gone now.

Thanks for your help

Helen









"Duke Carey" wrote:

OK Helen, let's try this again.

What do you THINK this formula does?

=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

It calculates a date based on another date, but what does this supposed
calculation signify? In other words, what date are you trying to calculate?
How does that calculated date interact with any of Q8, F16, and G16?

Next, how does the 'month (including the amount of days in it)' get into Q8?

How do the task start and end dates get into F16 and G16?

C'mon - we're trying to help you. We can't see your worksheet and the info
you have provided so far gives us NO CHANCE to decipher your problem.





"hellsbells" wrote:

Hi

1. the formula refers back to a cell which we put an initial project start
date into.
2. Q8 refers to the month (including the amount of days in it). F16 is the
task start date and G16 is the task end date.
3. These cells are to show on the spreadsheet the duration of the task and
how long the overall task takes.
4. i am trying to show the overall duration of the project i am working on
and how long each of the tasks within these last. basically its the sort of
thing that would normally be done in project but i dont have it unfortunately
so i have to try and produce this on excel.

i hope this helps and thanks for your help

"Duke Carey" wrote:

1) what do you *think* this formula does, and what cell is it in?
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

2) what is in cells Q8, F16, and G16?
3) how do those cells relate to the formula above?
4) most important - in plain English, what are you trying to accomplish?
Maybe your approach is convoluted and the people here could help you
streamline the approach - but we need to have an idea of your objective, and
that doesn't come across in either of your posts


"hellsbells" wrote:

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help

Hi Duke

this date relates to the project start date which we manually input into
cell E3. If we change the project start date in E3 then it should (in
theory) update all the other dates in the spreadsheet (excluding the task
dates which we manually input into cells F16 and G16 (all the dates in
columns f and g are manually inputted as start and end dates for the tasks))

Cell Q8 refers to cell I7 which has this formula in it
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+
(2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1)))) Q8 also shows the number of days in
the month in it too . the cells below this refer to Q8 and to cells F8 and
G8 (dates are manually inputted in these). in theory the cells that include
the months shown in F8 and G8 should be highlighted i.e. 01 march 07 to 18
June 07 - cells headed up march to june should be highlighted but only cells
march to may are in fact showing. It doesnt seem to like partial months and
this is the problem im having.

I have tried attaching a copy of the spreadsheet to this so you can see what
im talking about but it wont allow it sorry. I dont know if this clears it
up - if it doesnt im not sure what else i can do, and unfortunately the
person who designed this no longer works here so i cant ask them for the help.

Thanks a lot for your help

Helen

"Duke Carey" wrote:

1) what do you *think* this formula does, and what cell is it in?
=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))

2) what is in cells Q8, F16, and G16?
3) how do those cells relate to the formula above?
4) most important - in plain English, what are you trying to accomplish?
Maybe your approach is convoluted and the people here could help you
streamline the approach - but we need to have an idea of your objective, and
that doesn't come across in either of your posts


"hellsbells" wrote:

Hi Dave

I have set up the formula which will initially be used to set up a date this
then links to another field which will show this date. The information in
this field will then be linked to the field where we input the dates. if the
date is within the range then it will be highlighted but for some reason its
not highlighting it. If i changed the date to the end of the month then it
would highlight the section but if it is only part way through the month then
it leaves it blank.

here is a list of all formulas used:

"=DATE(YEAR(E3),MONTH(E3),1+((1-(2=WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))*31)+ (2-WEEKDAY(DATE(YEAR(E3),MONTH(E3),1))))"

=IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

and just a general = cell before plus 30

Im not sure if im explaining myself properly - sorry if im not

Thanks

Helen


"Dave F" wrote:

It's not clear how to help because you don't tell us what is value is in each
of the cells in your formula. The formula's syntax is correct, so absent any
information about the values being tested, we can't help.

Give us more detail about what values work, what values don't work, etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hellsbells" wrote:

I am writing a formula which will highlight cells with regard to specific
dates that have been put on the spreadsheet, however I can get it to work for
most of it but if the date is only a part way though the month it will not be
highlighted. I need it to be included - do you have any ideas how to get it
to work. The formula i am using is =IF(AND(Q$8=$F16,Q$8<=$G16),1,"").

Thanks for your help