Struggling with =IF(K7-J7<=1,"",J7+1)

[Michell Major]

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

 

[Bob Phillips]

=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]

Thanks Bob. it still returns a blank rather than the expected date
02/04/2007? Dan

 

[Roger Govier]

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(M23>1,$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.

 

[Michell Major]

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(M23>0,$N$2*M23))
O23 = formula for standing charge that all claimants pay... =IF(J23>1,$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]

Hi Dan

i have to go out right now, but if you want to send me the sheet I will
take a look on my return later this afternoon.
To send direct, remove NOSPAM from my email address.

 

[Roger Govier]

Hi Dan

File on its way back to you.

=IF(K19-J19>=1,J19+1,IF(AND(K19="",TODAY()>J19),J19+1,""))