More DVLA

[Guest]

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",AD77=""),$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?

 

[Roger Govier]

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
Tools>Options>View>Zero 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",AD77=""),$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.

 

[Guest]

Many thanks Roger

Hi
What does it return?
Are you sure C3 is Null and not a SPACE " ", or a 0 with
Tools>Options>View>Zero values unchecked?

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


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

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.

 

[Guest]

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]

Hi Dan

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