| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date formulas | Excel Discussion (Misc queries) | |||
| import /paste -stop automatic conversion of text 05-12345 to date | Excel Discussion (Misc queries) | |||
| Mileage Claim Formula | New Users to Excel | |||
| Can stop excel from converting value to date? | New Users to Excel | |||
| How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
Linear Mode
