Date STOP Accumulator

[Guest]

The columns on which to perform the arithmetic are

Date in: The initiating date +24 hours
Organisation: DVLA
Date Claimed: Date
Storage charge: £15 p.d. absolute reference Z2 which starts 24hrs after
receipt
Payment 1: £80 if the vehicle is collected within 24 hours - absolute
reference AB2
Payment 2: Absolute reference AC2, £160 if the vehicle is collected 24 hrs
+, and therefore blanks Payment 1
The formula I have is:
=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",AB204=""),$AC$2)))

I have 3 problems:
1. The formula above returns a FALSE value when L204 doesn't read 'DVLA'
2. I feel that a 'stop' command is needed somewhere to prevent a never
ending accumulation?
3. This formula: =IF(AND(L204="DVLA",P204=""),(TODAY()-A204)*$Z$2,"")
starts running the charges immediately rather than a day later.

Dan

 

[Roger Govier]

Hi Dan

=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",AB204=""),$AC$2)))

=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",AB204=""),$AC$2,"")))

To deal with the extra day, take 1 away from the days calculated by
Today()-A204
To stop it accumulating for ever, determine a Max_value and incorporate
as

=IF(AND(L204="DVLA",P204=""),MAX(max_value,(TODAY()-A204-1)*$Z$2),"")

 

[Guest]

Hi Roger. I don'tknow where you're based but you have been a saviour over
this week. Huge thanks for your solutions - all worked. Regards

dan

 

[Roger Govier]

Hi Dan

You're very welcome. Thanks for the feedback

I don'tknow where you're based

Only about 40 miles away from DVLA - just outside Abergavenny.