| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Struggling with =IF(K7-J7<=1,"",J7+1)
K7 = Date received
J7 = Date claimed L7 = Storage start date M7 = Elapsed days N = Storage O = Surety P = Fine 1 (i.e. claimed within 24 hours) Q = Fine 2 i.e. claimed over 24 hours There are other cells dependent on this group to work out costs. The formula works fine for STORAGE start date, apart from when a date is entered in K7 (say 01/04/2007) and J7 is blank. The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) =IF(J23="","",IF(J23-K23<1,$P$2,"")) should not display the P value if not claimed within 24 hours. =IF(M23="","",IF(M23=1,$Q$2)) works OK for the Fine 2 (Q) value. Help with all of this much appreciated. Dan |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Struggling with =IF(K7-J7<=1,"",J7+1)
=IF(OR(K7="",J7="",K7-J7<=1),"",J7+1)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michell Major" wrote in message ... K7 = Date received J7 = Date claimed L7 = Storage start date M7 = Elapsed days N = Storage O = Surety P = Fine 1 (i.e. claimed within 24 hours) Q = Fine 2 i.e. claimed over 24 hours There are other cells dependent on this group to work out costs. The formula works fine for STORAGE start date, apart from when a date is entered in K7 (say 01/04/2007) and J7 is blank. The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) =IF(J23="","",IF(J23-K23<1,$P$2,"")) should not display the P value if not claimed within 24 hours. =IF(M23="","",IF(M23=1,$Q$2)) works OK for the Fine 2 (Q) value. Help with all of this much appreciated. Dan |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Struggling with =IF(K7-J7<=1,"",J7+1)
Thanks Bob. it still returns a blank rather than the expected date
02/04/2007? Dan "Bob Phillips" wrote: =IF(OR(K7="",J7="",K7-J7<=1),"",J7+1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michell Major" wrote in message ... K7 = Date received J7 = Date claimed L7 = Storage start date M7 = Elapsed days N = Storage O = Surety P = Fine 1 (i.e. claimed within 24 hours) Q = Fine 2 i.e. claimed over 24 hours There are other cells dependent on this group to work out costs. The formula works fine for STORAGE start date, apart from when a date is entered in K7 (say 01/04/2007) and J7 is blank. The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) =IF(J23="","",IF(J23-K23<1,$P$2,"")) should not display the P value if not claimed within 24 hours. =IF(M23="","",IF(M23=1,$Q$2)) works OK for the Fine 2 (Q) value. Help with all of this much appreciated. Dan |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Struggling with =IF(K7-J7<=1,"",J7+1)
Hi Dan
DVLA still struggling to charge us these fines?? Not sure what you are really asking, as there is insufficient information =IF(J23="","",IF(J23-K23<1,$P$2,"")) Unless the claim date is the same as the receive date, then it will never be less than 1 Maybe you need the formula to be =IF(J23="","",IF(J23-K23<=1,$P$2,"")) and the other one =IF(M23="","",IF(M231,$Q$2)) However, you are also saying The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) You don't say what is in M7. Presumably something like =IF(K7="",TODAY()-L7,K7-L7) Is L7 an entered date or a calculated value? Maybe L7 should be =IF(K7="",J7+1,J7) Just guessing, as there is not enough info. -- Regards Roger Govier "Michell Major" wrote in message ... K7 = Date received J7 = Date claimed L7 = Storage start date M7 = Elapsed days N = Storage O = Surety P = Fine 1 (i.e. claimed within 24 hours) Q = Fine 2 i.e. claimed over 24 hours There are other cells dependent on this group to work out costs. The formula works fine for STORAGE start date, apart from when a date is entered in K7 (say 01/04/2007) and J7 is blank. The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) =IF(J23="","",IF(J23-K23<1,$P$2,"")) should not display the P value if not claimed within 24 hours. =IF(M23="","",IF(M23=1,$Q$2)) works OK for the Fine 2 (Q) value. Help with all of this much appreciated. Dan |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Struggling with =IF(K7-J7<=1,"",J7+1)
Roger
I was hoping you'd turn up. Yup the knotty problem of the DVLA - which is why I gave up my car and use a bicycle! It certainly beats London traffic but not sure I'd cope in Heads of the Valleys or Pen Y Pass. Anyway, here it goes ... J23 = blank date field RECEIVED manually entered K23 =blank date field CLAIMED manually entered. Yes, most drivers claim their cars within 24 hours. L23 = formula cell STORAGE START DATE where, if the car isn't claimed within 24 hours it inserts RECEIVED DATE + 1. M23 = formula cell where STORAGE date subtracts RECEIVED date to give ELAPSED days ... =IF(L23<"",IF(K23<"",MAX(K23-L23,1),TODAY()-L23+1),"") - which should stop when a date is entered in K23. N23 = formula; simple one, M23 * storage charge rate ... =IF(M23="","",IF(M230,$N$2*M23)) O23 = formula for standing charge that all claimants pay... =IF(J231,$O$2,"") P23 = formula where FINE1 is levied for claim within 24 hrs, otherwise blank... =IF(J23="","",IF(J23-K23<1,$P$2,"")) Q23 = formula where FINE2 is levied for claims over 24 hrs, but blank if P23 has a value ... =IF(M23="","",IF(M23=1,$Q$2)) I tried to send you the spreadsheet using the link in this forum but it was bounced back. Thanks in anticipation (TIA) Regards Dan "Roger Govier" wrote: Hi Dan DVLA still struggling to charge us these fines?? Not sure what you are really asking, as there is insufficient information =IF(J23="","",IF(J23-K23<1,$P$2,"")) Unless the claim date is the same as the receive date, then it will never be less than 1 Maybe you need the formula to be =IF(J23="","",IF(J23-K23<=1,$P$2,"")) and the other one =IF(M23="","",IF(M231,$Q$2)) However, you are also saying The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) You don't say what is in M7. Presumably something like =IF(K7="",TODAY()-L7,K7-L7) Is L7 an entered date or a calculated value? Maybe L7 should be =IF(K7="",J7+1,J7) Just guessing, as there is not enough info. -- Regards Roger Govier "Michell Major" wrote in message ... K7 = Date received J7 = Date claimed L7 = Storage start date M7 = Elapsed days N = Storage O = Surety P = Fine 1 (i.e. claimed within 24 hours) Q = Fine 2 i.e. claimed over 24 hours There are other cells dependent on this group to work out costs. The formula works fine for STORAGE start date, apart from when a date is entered in K7 (say 01/04/2007) and J7 is blank. The formula should recognise the received date and, if more than a day has elapsed between K7 and J7, add a day (02/04/2007 in L7) =IF(J23="","",IF(J23-K23<1,$P$2,"")) should not display the P value if not claimed within 24 hours. =IF(M23="","",IF(M23=1,$Q$2)) works OK for the Fine 2 (Q) value. Help with all of this much appreciated. Dan |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Struggling with =IF(K7-J7<=1,"",J7+1)
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
