a mail delivery problem

[ori]

hello, i have a very complex mail delivery problem that i need help with -

the columns are :account number (each account can show up several times),type (single or company),production date,message type (80 or else) and instant message (1,0)

the constraints are:

if instant message=1 then delivery date=production date

if message type=80 then delivery date=production date+10

if type=single then delivery date=production date+24

if type=company then delivery date=production date+14


messages that are not instant are sent on the 5,15,25 of every month

when deliver a message for a certain account number all messages that were produced until that date are sent with that message


i need to figure out the delivert date (i can't figure out how to do loops)

ex of datebase:

account # type prod. date m.type ins.message delivery date????

553 single 01/05/2011 80 0

626 single 03/05/2011 405 1

626 single 04/05/2011 451 0

626 single 11/05/2011 405 1

626 single 11/05/2011 750 0

626 single 11/05/2011 750 0

626 single 18/05/2011 405 1

626 single 25/05/2011 405 1

871 single 11/05/2011 750 0

871 single 31/05/2011 940 1


thank you

Ori

 

[fred]

please clarify the situation where "messages that are not instant are sent on the 5,15,25 of every month".


You want 3 delivery dates when instant message is = 0, and in current month/subsequent month?

 

[ori]

hi Fred,

thank you 4 your reply


the answer is yes, when instant message is = 0 there are 3 delivery dates


for ex if production date is 3/1/2011 and message =0 and type=single then delivery date is 3/1/11+24= 27/1/11 and because there are only 3 delivery dates (5,15,25 of every month) the actual delivery will be 5/2/2011

 

[fred]

conditions not completely described and I can only work on what I know.


suppose you have a delivery column for 1. I won't go over that because you can find it out on your own. I'll, however, chip in a bit based on only "single" and "0".


so my formula only works on "single", "0" and "non-80"

say ins.message if on column E and your computed delivery overall is on column F.


Column F would should the condition on "1" and the formula is IF(E2=1,C2,IF(D2=80,C2+10,IF(B2="single",C2+24,C2+14)))


In Column G the formula would be:

IF(E2=0,IF(DATE(YEAR(F2),MONTH(F2),5)-F2>=0,DATE(YEAR(F2),MONTH(F2),5),IF(DATE(YEAR(F2),MONTH(F2),15)-F2>=0,DATE(YEAR(F2),MONTH(F2),15),IF(DATE(YEAR(F2),MONTH(F2),25)-F2>=0,DATE(YEAR(F2),MONTH(F2),25),IF(MONTH(F2)=12,DATE(YEAR(F2)+1,1,5),DATE(YEAR(F2),MONTH(F2)+1,5))))))


copy down the column and if you see a "False" that mean that row is a "1" and you should go to column F for the delivery date.


my solution above encompasses a December scenario.