Conditional Formatting-dates
 

I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P) highlight
that the refresher course needs to be done in 3 years time e.i 22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of 1095
days as this will always be the timescale involved rather than individual
dates as I have thousands of rows, also can I set a condition in another
colum of 1035 days and sent this to an AMBER stage so we know that the course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

P2: = DATE(YEAR(E2)+3,MONTH(E2),DAY(E2))

Not sure what you are asking for the second part. I get the drift but not
the detail.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P)
highlight
that the refresher course needs to be done in 3 years time e.i 22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of 1095
days as this will always be the timescale involved rather than individual
dates as I have thousands of rows, also can I set a condition in another
colum of 1035 days and sent this to an AMBER stage so we know that the
course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

If i understand correctly you can put =A1+1095 in b1 if a1 has date, you can
highlight the whole column and select Format-Conditional formatting, hit the
dropdown and select formula and enter =A1+1095 and select your colors. if you
select the entire column it will automatically change the cell referenced.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eva" wrote:

I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P) highlight
that the refresher course needs to be done in 3 years time e.i 22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of 1095
days as this will always be the timescale involved rather than individual
dates as I have thousands of rows, also can I set a condition in another
colum of 1035 days and sent this to an AMBER stage so we know that the course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

Bob


I have tried this but it does not appear to work

the 2 columns that I need to format are the whole of col L or where the data
will be input L4,this is the completion date and in Columns 04 and P4 i need
to format so I can set a reminder condition at 1035 days(column 0) and also
column P4 the expiry date , I can set this col P to a date of 1095 days but
I'm not to sure of the formula, someting like P$1095 ?? as a cell value - I
am hoping to be able to use the date that the assessments are completed and
then use this then to set warning dates at 1035 days as amber and then the
expiry date as 1095-but I'm unsure where I need to set the conditions in
particular which columns needs what formula or cell value greater or less
than etc ...

Do I need to in each case select the whole column before so it will include
blank cells as well ???

Thanks, much better at Access but struglling with excel at the mo

--
Eva


"Bob Phillips" wrote:

P2: = DATE(YEAR(E2)+3,MONTH(E2),DAY(E2))

Not sure what you are asking for the second part. I get the drift but not
the detail.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P)
highlight
that the refresher course needs to be done in 3 years time e.i 22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of 1095
days as this will always be the timescale involved rather than individual
dates as I have thousands of rows, also can I set a condition in another
colum of 1035 days and sent this to an AMBER stage so we know that the
course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

I am still not sure I get it, but if you want to get the date 1035 days
after the date in L4, then in P4 use

=L4+1035

1095 is just =L4+1095.

You say to set a reminder at 1035 days, but 1035 from when, the original
date in L4, the completion date (P4?) or what?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
Bob


I have tried this but it does not appear to work

the 2 columns that I need to format are the whole of col L or where the
data
will be input L4,this is the completion date and in Columns 04 and P4 i
need
to format so I can set a reminder condition at 1035 days(column 0) and
also
column P4 the expiry date , I can set this col P to a date of 1095 days
but
I'm not to sure of the formula, someting like P$1095 ?? as a cell value -
I
am hoping to be able to use the date that the assessments are completed
and
then use this then to set warning dates at 1035 days as amber and then the
expiry date as 1095-but I'm unsure where I need to set the conditions in
particular which columns needs what formula or cell value greater or less
than etc ...

Do I need to in each case select the whole column before so it will
include
blank cells as well ???

Thanks, much better at Access but struglling with excel at the mo

--
Eva


"Bob Phillips" wrote:

P2: = DATE(YEAR(E2)+3,MONTH(E2),DAY(E2))

Not sure what you are asking for the second part. I get the drift but not
the detail.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P)
highlight
that the refresher course needs to be done in 3 years time e.i
22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of
1095
days as this will always be the timescale involved rather than
individual
dates as I have thousands of rows, also can I set a condition in
another
colum of 1035 days and sent this to an AMBER stage so we know that the
course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

Thanks Bob

Your a star , the date will be taken from L4 as this is the final asesssment
date, and P4 has to represent the 3 years expiry date from L4, Column )4 will
contain the reminder date set at 1035 days once again the dates in L4 will be
useed for this result.

Can I still use conditional formating so I can highlight the dates in
colours and will the format be via "cell value is greater to or less than" or
will it be a formula

Sorry but been thrown into the deep end and have not used excel for donkeys
years!!!

Eva
--
Eva


"Bob Phillips" wrote:

I am still not sure I get it, but if you want to get the date 1035 days
after the date in L4, then in P4 use

=L4+1035

1095 is just =L4+1095.

You say to set a reminder at 1035 days, but 1035 from when, the original
date in L4, the completion date (P4?) or what?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
Bob


I have tried this but it does not appear to work

the 2 columns that I need to format are the whole of col L or where the
data
will be input L4,this is the completion date and in Columns 04 and P4 i
need
to format so I can set a reminder condition at 1035 days(column 0) and
also
column P4 the expiry date , I can set this col P to a date of 1095 days
but
I'm not to sure of the formula, someting like P$1095 ?? as a cell value -
I
am hoping to be able to use the date that the assessments are completed
and
then use this then to set warning dates at 1035 days as amber and then the
expiry date as 1095-but I'm unsure where I need to set the conditions in
particular which columns needs what formula or cell value greater or less
than etc ...

Do I need to in each case select the whole column before so it will
include
blank cells as well ???

Thanks, much better at Access but struglling with excel at the mo

--
Eva


"Bob Phillips" wrote:

P2: = DATE(YEAR(E2)+3,MONTH(E2),DAY(E2))

Not sure what you are asking for the second part. I get the drift but not
the detail.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains dates
ddmmyyyy from these dates I need to be able in another column (P)
highlight
that the refresher course needs to be done in 3 years time e.i
22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of
1095
days as this will always be the timescale involved rather than
individual
dates as I have thousands of rows, also can I set a condition in
another
colum of 1035 days and sent this to an AMBER stage so we know that the
course
will need to be re-booked, what formula do I need in each instance ??

Version-Excel 2003
Many Thanks
--
Eva

Conditional Formatting-dates
 

I am missing why you need anything in O4. L4 is the final assessment date,
P4 is L4+1035, and if you want CF just select L4 and P4 and use a formula of

=$P4=TODAY()

and highlight with some colour.

You could add another condition to warn you as the date approaches, with a
formula of

=$P4=TODAY()-30

which gives you a 30 day warning, with a different highlight colour of
course

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
Thanks Bob

Your a star , the date will be taken from L4 as this is the final
asesssment
date, and P4 has to represent the 3 years expiry date from L4, Column )4
will
contain the reminder date set at 1035 days once again the dates in L4 will
be
useed for this result.

Can I still use conditional formating so I can highlight the dates in
colours and will the format be via "cell value is greater to or less than"
or
will it be a formula

Sorry but been thrown into the deep end and have not used excel for
donkeys
years!!!

Eva
--
Eva


"Bob Phillips" wrote:

I am still not sure I get it, but if you want to get the date 1035 days
after the date in L4, then in P4 use

=L4+1035

1095 is just =L4+1095.

You say to set a reminder at 1035 days, but 1035 from when, the original
date in L4, the completion date (P4?) or what?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
Bob


I have tried this but it does not appear to work

the 2 columns that I need to format are the whole of col L or where the
data
will be input L4,this is the completion date and in Columns 04 and P4 i
need
to format so I can set a reminder condition at 1035 days(column 0) and
also
column P4 the expiry date , I can set this col P to a date of 1095 days
but
I'm not to sure of the formula, someting like P$1095 ?? as a cell
value -
I
am hoping to be able to use the date that the assessments are completed
and
then use this then to set warning dates at 1035 days as amber and then
the
expiry date as 1095-but I'm unsure where I need to set the conditions
in
particular which columns needs what formula or cell value greater or
less
than etc ...

Do I need to in each case select the whole column before so it will
include
blank cells as well ???

Thanks, much better at Access but struglling with excel at the mo

--
Eva


"Bob Phillips" wrote:

P2: = DATE(YEAR(E2)+3,MONTH(E2),DAY(E2))

Not sure what you are asking for the second part. I get the drift but
not
the detail.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Eva" wrote in message
...
I have a spreadsheet for training dates and need to be able to create
formating on a couple of the columns, the 4th colom (E),contains
dates
ddmmyyyy from these dates I need to be able in another column (P)
highlight
that the refresher course needs to be done in 3 years time e.i
22/12/2006
refresher course 22/12/2009 can I also put in a generic timescale of
1095
days as this will always be the timescale involved rather than
individual
dates as I have thousands of rows, also can I set a condition in
another
colum of 1035 days and sent this to an AMBER stage so we know that
the
course
will need to be re-booked, what formula do I need in each instance
??

Version-Excel 2003
Many Thanks
--
Eva