Date STOP Accumulator
 

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",A B204=""),$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

Date STOP Accumulator
 

Hi Dan

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

=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",A B204=""),$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),"")


--
Regards

Roger Govier


"Michell Major" wrote in
message ...
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",A B204=""),$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

Date STOP Accumulator
 

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" wrote:

Hi Dan

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

=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",A B204=""),$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),"")


--
Regards

Roger Govier


"Michell Major" wrote in
message ...
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",A B204=""),$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

Date STOP Accumulator
 

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.

--
Regards

Roger Govier


"Michell Major" wrote in
message ...
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" wrote:

Hi Dan

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

=IF(L204="","",IF(AB204=80,"",IF(AND(L204="DVLA",A B204=""),$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),"")


--
Regards

Roger Govier


"Michell Major" wrote in
message ...
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",A B204=""),$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