Formula pain part2 1

[Brimestone]

Hey guys, I need help on reworking this formula you helped me with a few years ago, thread68-1330475

=B6+MIN(5000/D9,150)+(TEXT(B6+MIN(5000/D9,150),"ddd")="Sun")

It figures mileage and a date for our customers next appointment but not to pick Sunday. Now we don't want to pick Saturday or Sunday. Ask me anything to do with cars, and I can fix it, but this stuff kills me every time.

Thanks for your help

Rob



Computers are like a bag of hammers, they won't do a damn thing until you pick it up and SMASH something with it!!

 

[SkipVought]

Hi,
[tt]
=B6+MIN(5000/D9,150)
+(or(TEXT(B6+MIN(5000/D9,150),"ddd")="Sat",TEXT(B6+MIN(5000/D9,150),"ddd")="Sun"))
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Brimestone]

Thanks skip, but it didn't work, the first argument did as expected, it move the appointment date up a day to Sunday but the second argument didn't do anything and left the day on Sunday

Any other ideas, I tried a few things but couldn't get it to work, if only I could write a script in basic I could make it work. IF-THEN arguments are more predicable.

I should stick to fixing cars...

Thanks

Computers are like a bag of hammers, they won't do a damn thing until you pick it up and SMASH something with it!!

 

[SkipVought]

How about posting some sample data and the expected result.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi Rob,

You could try:
=B6+MIN(INT(5000/D9),150)+(MOD(B6+MIN(INT(5000/D9),150),7)=0)*2+(MOD(B6+MIN(INT(5000/D9),150),7)=1)

Cheers

[MS MVP - Word]

 

[Brimestone]

Thanks macropod, that works perfectly, maybe in a week or two I'll figure out why it works, but thanks again.

Rob

Computers are like a bag of hammers, they won't do a damn thing until you pick it up and SMASH something with it!!