Formula pain... 4

[Brimestone]

We have a spread sheet to calculate when customers need to come back for their next scheduled oil change. It works really well calculating the mileage verses time and will produce the next date. The problem is when it picks a “Sunday” we would like it to add one day to the date. So, in the cell the formula is in, will display on the screen something like this “Sunday, February 04, 2007”

I tried this formula to see if it could tell there was a Sunday in the cell =IF(D12 ="Sunday"), but only resulted in a FALSE. Not sure why, this operator should see the Sunday in the field.

Any suggestions would be helpful

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!!

 

[straybullet]

=IF((WEEKDAY(A1+17,2))=7,A1+18,A1+17)

substitute your current calculation for the next appointment date for the red text and be sure to format the column as Date.

Date Date+17 Next Appt
2/2/2007 2/19/2007 2/19/2007
2/1/2007 2/18/2007 2/19/2007
1/31/2007 2/17/2007 2/17/2007

Let them hate - so long as they fear... Lucius Accius

 

[SkipVought]

Hi,

Assuming that your date is in a1...
[tt]
B1: =if(text(a1,"ddd")="Sun",1,0)+a1
[/tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[straybullet]

Thank you Skip! Always nice to see an easier way of doing things!

Let them hate - so long as they fear... Lucius Accius

 

[NoCalAdmin]

Skip is the MAN... I star almost every posting he puts out there!!! never-ending education comes from him...

Tom

 

[Brimestone]

Thanks for your help, I'll try those formulas and let you know how it worked out, if I have any problems I'll be asking 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!!

 

[Brimestone]

Thanks Skip, the formula works great.

Now it's not the end of the world if I can't it nested with the formula that creates the date, but it would clean up the work sheet.

Here is the formula:
=IF(5000/D9+B6>B6+150,(B6+150),(5000/D9+B6))
It basicaly takes the mileage and date of the last oil change, calculates the next oil service by comparing the date vs mileage, which ever will occur first, 5000km or 5 months.

D9 is the Average KM per day, B6 is the date of the current appontment.

thank for your help

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

 

[macropod]

Hi Brimestone ,

Try:
=IF(5000/D9+B6>B6+150,B6+150+(MOD(B6+150,7)=1),5000/D9+B6+(MOD(5000/D9+B6,7)=1))

Cheers

[MS MVP - Word]

 

[TonyJollans]

=IF(5000/D9+B6>B6+150,(B6+150),(5000/D9+B6))

You are comparing (x+B6) with (y+B6). Why not just compare x and y?

[tt] =IF(5000/D9>150,(B6+150),(5000/D9+B6))[/tt]

Of course you can make it simpler by pulling the "B6" outside the condition ..

[tt] =B6+IF(5000/D9>150,150),5000/D9)[/tt]

It now becomes clear that what you want to add is just the lower of 5000/B9 and 150 so you can simplify it to:

[blue][tt] =B6+MIN(5000/D9,150)[/tt][/blue]

Now to check that this is a Sunday using Skip's check ...

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

... returns True or False which can be coerced to numeric values of 1 and 0 respectively and which can, thus, just be added to the date ...

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

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Brimestone]

Thanks Tony, I see what your saying and I will try that, looks like it would work better.

Thanks

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!!

 

[Brimestone]

Thanks again Tony, Its works flawlessly.

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!!