#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default More DVLA

There are several strings to this question.

1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time
between C3 and today if B3 is blank - but doesn't.

2) The formulae work OK except they return FALSE when cell N78 (DVLA cell)
has anything other than DVLA.
Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)
Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

3) Invoices are raised every month for vehicles kept in store. Two things;
I need to a) carry-over to a new worksheet, vehicles that remain unclaimed
and in store. and b) reset the clock to accumulate the next months' storage
charge.

Finally. When this has all been piloted for a month or twoI want to migrate
it to MS ACCESS so that data can be kept clean. Does anyone know whether the
Excel calculations migrate to Access?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default More DVLA

Hi
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't

What does it return?
Are you sure C3 is Null and not a SPACE " ", or a 0 with
ToolsOptionsViewZero values unchecked?

Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)

should be
=IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"")


Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

should be
=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,"")))

a) carry-over to a new worksheet, vehicles that remain unclaimed

Take a look at Advanced Filter
Debra Dalgleish has some great guidance on her site
http://www.contextures.com/xladvfilter01.html#ExtractWs

b) reset the clock to accumulate the next months' storage charge.
you don't say what the clock is?
Presumably a cell on the sheet which holds reference date.

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
There are several strings to this question.

1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't.

2) The formulae work OK except they return FALSE when cell N78 (DVLA
cell)
has anything other than DVLA.
Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)
Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

3) Invoices are raised every month for vehicles kept in store. Two
things;
I need to a) carry-over to a new worksheet, vehicles that remain
unclaimed
and in store. and b) reset the clock to accumulate the next months'
storage
charge.

Finally. When this has all been piloted for a month or twoI want to
migrate
it to MS ACCESS so that data can be kept clean. Does anyone know
whether the
Excel calculations migrate to Access?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default More DVLA

Many thanks Roger

"Roger Govier" wrote:

Hi
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't

What does it return?
Are you sure C3 is Null and not a SPACE " ", or a 0 with
ToolsOptionsViewZero values unchecked?

Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)

should be
=IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"")


Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

should be
=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,"")))

a) carry-over to a new worksheet, vehicles that remain unclaimed

Take a look at Advanced Filter
Debra Dalgleish has some great guidance on her site
http://www.contextures.com/xladvfilter01.html#ExtractWs

b) reset the clock to accumulate the next months' storage charge.
you don't say what the clock is?
Presumably a cell on the sheet which holds reference date.

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
There are several strings to this question.

1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't.

2) The formulae work OK except they return FALSE when cell N78 (DVLA
cell)
has anything other than DVLA.
Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)
Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

3) Invoices are raised every month for vehicles kept in store. Two
things;
I need to a) carry-over to a new worksheet, vehicles that remain
unclaimed
and in store. and b) reset the clock to accumulate the next months'
storage
charge.

Finally. When this has all been piloted for a month or twoI want to
migrate
it to MS ACCESS so that data can be kept clean. Does anyone know
whether the
Excel calculations migrate to Access?




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default More DVLA

Roger. I made a mess of the original formula so have corrected it. However
it still doesn't work and returns VALUE! A3 is date received, B3 is date
actioned,C3 is where the formula is for elapsed time. The simple formula is
B3-A3=elapsed time.

And yes, to the 3rd string question, the clock start time is A3 when we
receive the vehicle.

"Roger Govier" wrote:

Hi
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't

What does it return?
Are you sure C3 is Null and not a SPACE " ", or a 0 with
ToolsOptionsViewZero values unchecked?

Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)

should be
=IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"")


Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

should be
=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,"")))

a) carry-over to a new worksheet, vehicles that remain unclaimed

Take a look at Advanced Filter
Debra Dalgleish has some great guidance on her site
http://www.contextures.com/xladvfilter01.html#ExtractWs

b) reset the clock to accumulate the next months' storage charge.
you don't say what the clock is?
Presumably a cell on the sheet which holds reference date.

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
There are several strings to this question.

1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't.

2) The formulae work OK except they return FALSE when cell N78 (DVLA
cell)
has anything other than DVLA.
Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)
Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

3) Invoices are raised every month for vehicles kept in store. Two
things;
I need to a) carry-over to a new worksheet, vehicles that remain
unclaimed
and in store. and b) reset the clock to accumulate the next months'
storage
charge.

Finally. When this has all been piloted for a month or twoI want to
migrate
it to MS ACCESS so that data can be kept clean. Does anyone know
whether the
Excel calculations migrate to Access?




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default More DVLA

Hi Dan

My guess is still that cell A3 contains a space.
What do you get for =LEN(A3)?

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
Roger. I made a mess of the original formula so have corrected it.
However
it still doesn't work and returns VALUE! A3 is date received, B3 is
date
actioned,C3 is where the formula is for elapsed time. The simple
formula is
B3-A3=elapsed time.

And yes, to the 3rd string question, the clock start time is A3 when
we
receive the vehicle.

"Roger Govier" wrote:

Hi
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't

What does it return?
Are you sure C3 is Null and not a SPACE " ", or a 0 with
ToolsOptionsViewZero values unchecked?

Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)

should be
=IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"")


Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

should be
=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,"")))

a) carry-over to a new worksheet, vehicles that remain unclaimed

Take a look at Advanced Filter
Debra Dalgleish has some great guidance on her site
http://www.contextures.com/xladvfilter01.html#ExtractWs

b) reset the clock to accumulate the next months' storage charge.
you don't say what the clock is?
Presumably a cell on the sheet which holds reference date.

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
There are several strings to this question.

1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the
elapsed time
between C3 and today if B3 is blank - but doesn't.

2) The formulae work OK except they return FALSE when cell N78
(DVLA
cell)
has anything other than DVLA.
Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2)
Option
2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2)))

3) Invoices are raised every month for vehicles kept in store. Two
things;
I need to a) carry-over to a new worksheet, vehicles that remain
unclaimed
and in store. and b) reset the clock to accumulate the next months'
storage
charge.

Finally. When this has all been piloted for a month or twoI want
to
migrate
it to MS ACCESS so that data can be kept clean. Does anyone know
whether the
Excel calculations migrate to Access?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"