Conditional Formatting - Weeks VOR
 

i have a formulae "=SUM(TODAY()-G7)/7" which calculates the difference
between the value in column G and todays date and then converts it into
number of whole weeks.

the value in column G is the date on which each vehicle came off the road to
be repaired, if the vehicle is still in use theres no date in column G, when
that happens i get the value of 5612 this is because if column G is empty it
assumes the value as 01/01/1990, what i want to happen is if the cell in
column g has nothing in it then i want it to give a result of " " so the cell
is empty, the reason i need that value returned is that the weeks vor column
is used to compile graphs on how long vehicles have been off road if its not
off road then i dont want it showing as having been off road for over 5000
weeks as thats not helpful to me or my customer whom this report is sent to,
what formulae can i use to do what i want to do?

--
Stuart

Conditional Formatting - Weeks VOR
 

Hi

=IF(G7="","",(TODAY()-G7)/7)
, or, when you want only the number of full weeks
=IF(G7="","",INT((TODAY()-G7)/7))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Stuart" wrote in message
...
i have a formulae "=SUM(TODAY()-G7)/7" which calculates the difference
between the value in column G and todays date and then converts it into
number of whole weeks.

the value in column G is the date on which each vehicle came off the road
to
be repaired, if the vehicle is still in use theres no date in column G,
when
that happens i get the value of 5612 this is because if column G is empty
it
assumes the value as 01/01/1990, what i want to happen is if the cell in
column g has nothing in it then i want it to give a result of " " so the
cell
is empty, the reason i need that value returned is that the weeks vor
column
is used to compile graphs on how long vehicles have been off road if its
not
off road then i dont want it showing as having been off road for over 5000
weeks as thats not helpful to me or my customer whom this report is sent
to,
what formulae can i use to do what i want to do?

--
Stuart

Conditional Formatting - Weeks VOR
 

thank you that worked perfectly.
--
Stuart


"Arvi Laanemets" wrote:

Hi

=IF(G7="","",(TODAY()-G7)/7)
, or, when you want only the number of full weeks
=IF(G7="","",INT((TODAY()-G7)/7))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Stuart" wrote in message
...
i have a formulae "=SUM(TODAY()-G7)/7" which calculates the difference
between the value in column G and todays date and then converts it into
number of whole weeks.

the value in column G is the date on which each vehicle came off the road
to
be repaired, if the vehicle is still in use theres no date in column G,
when
that happens i get the value of 5612 this is because if column G is empty
it
assumes the value as 01/01/1990, what i want to happen is if the cell in
column g has nothing in it then i want it to give a result of " " so the
cell
is empty, the reason i need that value returned is that the weeks vor
column
is used to compile graphs on how long vehicles have been off road if its
not
off road then i dont want it showing as having been off road for over 5000
weeks as thats not helpful to me or my customer whom this report is sent
to,
what formulae can i use to do what i want to do?

--
Stuart