Ok so I am trying to calculate something regarding dates. Is there something wrong with this formula? The answer to the first cell is correct but when I copy it down it does not calculate it correctly. Im thinking if there is something wrong with it is that there are too many IF's. Thank you =IF(A5-$I$25>H21,I21,IF(A5-$I$25>H22,I22,IF(A5-$I$25>H23,I23,IF(A5-$I$25>H24,I24,0))))

Read this How-To http://www.computing.net/howtos/sho...

Then post a sample of your data that corresponds to the formula.

If your dealing with DATES, then make sure they are all formatted as such, and not as TEXT.

Your formula appears to be OK.

MIKE

You have to pay before the start date. If you pay on the first day and through the 1st week you get charged 5 if its 2 weeks its 10 and so on. If you pay before you dont ge charged. Thanks ABCD8/10/97Week 158/17/97Week 2108/24/97Week 3158/31/97Week 4209/7/979/14/97Start date 8/16/97

This one is better. You have to pay before the start date. If you pay on the first day and through the 1st week you get charged 5 if its 2 weeks its 10 and so on. If you pay before you dont ge charged. Thanks ABC8/10/97Week 158/17/97Week 2108/24/97Week 3158/31/97Week 4209/7/979/14/97Start date 8/16/97

Read this How-To http://www.computing.net/howtos/sho...

Then post a sample of your data from your spread sheet that corresponds to the formula.

Your formula uses columns A, H and I

I don't understand how your last post relates to your formula.

MIKE

Columns B and C would be used as H and I. I didnt want to enter that many columns and the rest of the useless information so I just entered what was necessary. So columns B and C represent columns H and I.

re: " So columns B and C represent columns H and I."Come on, Mike! You couldn't figure that out? Sheesh.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

DerbyDad03 instead of sitting here and clowning on people who ask question maybe it would be easier not to do that instead of wasting your time on that and maybe even asnwer a question or two. Obviously I made a mistake and didn't mention that which was my fault. But you are very good at asking questions that have nothing to do with answering people posts. If you don't like me for some reason thats fine but why waste your time and post negative stuff and try and make people feel bad?

The reason I asked you to post from your spreadsheet, after reading the indicated How To, is so I don't have to spend ten minutes copying your data and then having to transposing it all before I can even begin to try and figure out what your problem is.

If you read the How To, and then copy and paste an example of your spreadsheet, it saves me the time of trying to interpret what you mean and figure out exactly what goes where.

I figured what most of your data means, but what and where does that very last line of your post go?

That would be the line that reads Start Date.

In Cell D1 or someplace else?

I didnâ€™t want to enter that many columns and the rest of the useless information so I just entered what was necessary.

But you did not read the How To.

If you need to post your data then create a test sheet with only the data that you need and post that.Your not a newbie here, you should know how to post your data by now.

MIKE

I have read it I used the Pre tags but everytime I try to post something it comes out weird. It has only worked once correctly. Well honestly the start date can be wherever because we use that date to subtract. I put it in C6. I feel like everytime I write something its more confusing.

Is this what your data looks like?: A H I 5) 8/16/1997 21) Week 1 5 22) Week 2 10 23) Week 3 15 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997If it is, and your using the formula:

=IF(A5-$I$25>H21,I21,IF(A5-$I$25>H22,I22,IF(A5-$I$25>H23,I23,IF(A5-$I$25>H24,I24,0))))Then your trying to compare a NUMBER with the TEXT string

Week 1A5 = 8/16/1997 minus I25 = 8/10/1997 equals the NUMBER 6

that make your formula read: IF(A5-I25>WEEK 1

Or does your data look like:A H I 5) 8/16/1997 21) 5 Week 1 22) 10 Week 2 23) 15 Week 3 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997Then modify the formula:

=IF($A$5-I25>$H$21,$I$21,IF($A$5-I25>$H$22,$I$22,IF($A$5-I25>$H$23,$I$23,IF($A$5-I25>$H$24,$I$24,0))))

An then the formula will return Week 1 for row 25,

as Row 26 is only 1 day greater and row 27 is -8 days, they will both return zero.MIKE

re: " ..maybe even asnwer a question or two."OK, I'll give that a try.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

A H I

8/10/97 Week 1 5

8/17/97 Week 2 10

8/24/97 Week 3 15

8/31/97 Week 4 20

9/7/97

9/14/97 Start Date 8/17/97Sorry I listed the start date incorrectly on the last one. This is how the chart should look. It doesn't look normal again it looks weird but this is the correct way. Sorry for the whole confusion

A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97Ok, we have the column letters,

now try for the Row numbers.

When you post using the pre tags, replace the tabs with spaces.First copy you data into a text editor like Notepad or Jedit then search/replace all the tabs with spaces, the post using the pre tags.

See if that makes it easier.

Were either of my examples correct?

MIKE

Yes just like that. Thank you

A H I

21 8/10/97 Week 1 5

22 8/17/97 Week 2 10

23 8/24/97 Week 3 15

24 8/31/97 Week 4 20

25 9/7/97

26 9/14/97 Start Date 8/17/97

Sorry just like that was to DerbyDad but you posted before I replied. So no matter how I try to post it correctly it does not work. But this is how it should look. Sorry for driving you crazy.

Cali, your making this much harder then it has to be. Your formula is:

=IF(A5-$I$25>H21,I21,IF(A5-$I$25>H22,I22,IF(A5-$I$25>H23,I23,IF(A5-$I$25>H24,I24,0))))

you show as dataA H I 21) 8/10/97 Week 1 5 22) 8/17/97 Week 2 10 23) 8/24/97 Week 3 15 24) 8/31/97 Week 4 20 25) 9/7/97 26) 9/14/97 Start Date 8/17/97Where is A5?

Is A5 now supposed to be I26?Again, were either of the two format examples given in reply #10 correct?

Are you subtracting the date in I26 from the dates in column A?

or are you subtracting the dates in column A from the date in I26?I believe your problem is which cells you anchor.

MIKE

I apologize. Yes I am subtracting the dates in columns A from I26. So the first one would be 0 I know that but when I copy that formula down it doesn't give me the correct answers.

Yes it looks like it is correct.

I am subtracting the dates in columns A from I26.I26 = 8/17/1997

A B C 1) 8/10/1997 8/17/1997 7 < formula B1-A1 2) 8/17/1997 8/17/1997 0 < formula B2-A2 3) 8/24/1997 8/17/1997 -7 < formula B3-A3 4) 8/31/1997 8/17/1997 -14 < formula B4-A4 5) 9/07/1997 8/17/1997 -21 < formula B5-A5 6) 9/14/1997 8/17/1997 -28 < formula B6-A6Is this what your looking for?

or this:

A B C 1) 8/10/1997 8/17/1997 -7 < formula A1-B1 2) 8/17/1997 8/17/1997 0 < formula A2-B2 3) 8/24/1997 8/17/1997 7 < formula A3-B3 4) 8/31/1997 8/17/1997 14 < formula A4-B4 5) 9/07/1997 8/17/1997 21 < formula A5-B5 6) 9/14/1997 8/17/1997 28 < formula A6-B6MIKE

No, sorry I wanted to mention what I was looking for again but didn't want to make it confusing. What im looking for is if a fee is paid before the start date there is no charge, if it is paid on the 1st day and throughout the first week they would get charged 5 since it is 1 week late, if it is 2 weeks they would get charged 10, 3 weeks 15, 4 weeks or more 20.

So it should be

If DATE = START DATE plus 1 - 7 days = 5

If DATE = START DATE plus 8 - 14 days = 10

If DATE = START DATE plus 15 - 21 days = 15MIKE

Im not sure what that means it should be if the fee is paid before start date no charge. If it is paid on start date and till the 7 day then 5, 8-14=10, 15-21=15, so if that is what your saying then yes. But again date paid subtracted from start date. Thank you soooooo much I really appreciate your help.

Let's get back to basics. Your formula is testing for the difference between two DATES,

A B C 1) 8/10/1997 8/17/1997 7 < formula B1-A1 2) 8/17/1997 8/17/1997 0 < formula B2-A2 3) 8/24/1997 8/17/1997 -7 < formula B3-A3 4) 8/31/1997 8/17/1997 -14 < formula B4-A4 5) 9/07/1997 8/17/1997 -21 < formula B5-A5 6) 9/14/1997 8/17/1997 -28 < formula B6-A6In row one above:

the DATE, 8/17/1997 minus the DATE 8/10/1997 equal 7 days becuse the 17th is GREATER and the 10thIn row three above

the DATE, 8/17/1997 minus the DATE 8/24/1997 equal -7 days because the 24th is GREATER than the 17thThe first part of your formula is: =IF( A5 - $I$25 > H21

What values do you have in A5

What values do you have in I25

What values do you have in H21MIKE

The value in A5 is 8/10/97 the value in I25 is the start date which is 8/17/97 the value in H21 it says week 1. And since it says week 1 they should get charged 5. Would it be easier if I gave you the answers?

So your formula looks like this: The first part of your formula is:

=IF( A5 - $I$25 > H21

=IF(8/10/97 - 8/17/97 > week 1 DATE - DATE > TEXTYou tell me what's wrong....

MIKE

Numbers are mixed with text?

Numbers are mixed with text?Correct.

When you subtract dates you get a number.

So how do you correct your formula?

MIKE

I don't know maybe not use that part?

Review what I wrote in reply #10 it should give you an idea of what you need to do. Also, try again to post an example of your data, if your going to be posting here, you should know how to do this.

Step 1. Click the Pre icon found above the Reply box.

Step 2. Enter your data/table(s) or VBA codebetween the tags.

Step 3. ClickPreview Follow Upbelow on the right, DO NOT press Submit Follow Up, to see if you like the way it looks.

Step 4. If you need to fix the layout, the top window shows what your messages looks like, the bottom window allows you to fix and modify your message. Make your changes.

Step 5. At the top there is a box with "Check To Show Confirmation Page Again" check this box.

Step 6. Click either Confirm button to Preview the post again.

You can now edit your message again, make your changes/edits and to review these new edits start at step 5.

You can keep making corrections until you get it the way you want.

If your going to be doing DATE math, I would advise you to review the =DATEDIF() function, it might make your life easier or confuse you even more.MIKE

In reply 10 you talk about modifying the formula. Ok so I use the Pre Icon button but it does not post correctly. I tried moving the Pre Icon button as well but regardless of what I do it does not post correctly. It comes out like this

A

B

C

When you click on the pre button,

two symbols should appear in your message,

they should should be the word PRE

surrounded by left & right arrows

they should look like:< pre > < / pre >

I've added spaces between everything to highlight what they look like, they will not have the spaces normally.

Post your data between the two symbols:

< pre >

A B C 1) Data Goes here 2) more data here

< / pre >This will keep the columns aligned.

When you preview your message, you can now modify the columns so everything lines up correctlyMIKE

Here you go. I got it A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97

Excellent.. Now how do you fix your formula?

First do a simple =IF() function to begin, it should be along the lines of:

=IF( DATE - DATE > NUMBER, TEXT, 0 )

MIKE

=IF(A5-$I$25>I21,H21,0)?

A H I 1) 8/10/97 Week 1 5 2) 8/17/97 Week 2 10 3) 8/24/97 Week 3 15 4) 8/31/97 Week 4 20 5) 9/7/97 6) 9/14/97 Start Date 8/17/97Did you use the above data for your formula??

MIKE

$I$6-A1>I1,H1,0? I really don't think that is correct but this is the best I can think of

So work out the results. I6 = 8/17/97

A1 = 8/10/97I6 - A1 equals 7 days

Is seven days, greater than the contents of I1 ?

If true, then show the contents of H1

If false, then show zeroThat is how your formula:

=IF($I$6-A1>I1,H1,0)

will work out.

Now, what would be a simple =IF() formula to test for week 2?

MIKE

But I is not dates I is money so I dont think that is right

But I is not dates I is money so I dont think that is rightGot me confused now.

Post a copy of your worksheet, with column letters and row numbers.

Explain what each column represents, and what each row represents.

Explain what your formula should do.Showwhat your expected results should look like.MIKE

A H I J is for the results Results 1. 8/10/1997 Week 1 $5 $0 since they paid before the start date 2. 8/17/1997 Week 2 $10 $5 since its 1 week late 3. 8/24/1997 Week 3 $15 $10 since its 2 weeks late 4. 8/31/1997 Week 4 $20 $15 since its 3 weeks late 5. 9/7/1997 $20 since its 4 weeks late 6. 9/14/1997 Start Date 8/17/1997 $20 since its over 4 weeks late

Column A is the date paid.

Column B is Weeks

Column C is how much to charge per each week.So if someone paid before the start date they pay $0 fees. If they paid on the 1st day and throughout the 1st week they would get charged $5, week 2 $10, week 3 $15, week 4 and on $20.

OK, I think I see what's going on: You can't compare NUMBERS & TEXT, and since column H is TEXT, we have to give a static number for the comparison,

so your formula should be something like:=IF(A5-$I$25>"7",I21,

=IF( DATE - DATE > NUMBER, TEXT, 0 )

The number 7 is for Week 1, seven days.

MIKE

Thats not the whole formula though right?

Thats not the whole formula though right?You catch on quick. :-)

So what is the rest of the formula?MIKE

Im not sure if you making fun of me but is it another IF?

Im not sure if you making fun of me but is it another IF?I would never make fun of you, which leaves another IF, or just modify your original

=IF(A5-$I$25>"7",I21, You've got this far now what? IF(A5-$I$25>H22,I22, IF(A5-$I$25>H23,I23, IF(A5-$I$25>H24,I24,0))))MIKE

IF(A5-$I$25>14,I22,

IF(A5-$I$25>21,I23,

IF(A5-$I$25>28,I24,0))))????

Does it work? =IF(A5-$I$25>7,I21,IF(A5-$I$25>14,I22,IF(A5-$I$25>21,I23,IF(A5-$I$25>28,I24,0))))

MIKE

No it does not. It doesnt produce the answers im supposed to get

I get 0,0,0,20,35659,0. I should be getting 0,5,10,15,20,20

I should be getting 0,5,10,15,20,20

What formula are you using?

What cells are you using?

Post your spreadsheet.

With column headings and row numbers,

also what formulas your using in relation to what cells?MIKE

Im using everything exactly from above the formulas and the spreadsheet

There are a number of examples above, which one?

( I have three different configurations of your data....)We've done a number of formulas, which one?

You have the spreadsheet in front of you, I can't see what's on it or what your doing.

You have to post it so I can see it.

Post everything.

Column Headings,

Row Numbers,

Cells Used

Contents of Cells

FormulasMIKE

Is there a way I can post the file on here?

No, only using the pre tags. A H I 5) 8/16/1997 21) Week 1 5 22) Week 2 10 23) Week 3 15 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997Is this what your data looks like?

Note the row numbers and column letters.MIKE

Ok I think I made a mistake when posting. It is exactly like this A H I 5) 8/10/97 6) 8/17/97 7) 8/24/97 8) 8/31/97 9) 9/7/97 10) 9/14/97 20) Week 21) 1 $5 22) 2 $10 23) 3 $15 24) 4 $20 25) Start date 8/17/97I hope this helps

In your formula you forgot to anchor the cells containing the amounts: =IF(A5-$I$25>7,$I$21,IF(A5-$I$25>14,$I$22,IF(A5-$I$25>21,$I$23,IF(A5-$I$25>28,$I$24,0))))

There are TWO additional things you have to do to get this to work,

One:

what happens if the date subtraction equalsexactly7?

Your formula uses just the GREATER than symbol.MIKE

Is it >=?

Correct. So now your formula looks like:

=IF(A5-$I$25>=7,$I$21,IF(A5-$I$25>=14,$I$22,IF(A5-$I$25>=21,$I$23,IF(A5-$I$25>=28,$I$24,0))))

The last problem is a bit tricky, and you are not the first to be stumped by it.

An =IF() function

stopsafter it finds a true statement, it does not continue down the formula to the end.So if your calculation of dates comes out to 14, you first test returns true, because 14 is greater than 7.

But, this is NOT what your looking for, you need it to check further down the formula.

Can you figure out how to correct this final problem?MIKE

No I really don't know what else is missing

OK, I'll give you this one. You need to reverse the sequence of your test.

Test the higher numbers first.So your formula should be:

=IF(A5-$I$25>=28,$I$24,IF(A5-$I$25>=21,$I$23,IF(A5-$I$25>=14,$I$22,IF(A5-$I$25>=7,$I$21,0))))

Notice we check for 28 first, then 21, etc

Try it and see if it works.

MIKE

I see what you did but it stil does not give me the correct results.

It works for me. A B H I 5) 8/10/97 0 <=IF(A5-$I$25>=28,$I$24,IF(A5-$I$25>=21,$I$23,IF(A5-$I$25>=14,$I$22,IF(A5-$I$25>=7,$I$21,0)))) 6) 8/17/97 0 < 7) 8/24/97 5 < 8) 8/31/97 10 < 9) 9/7/97 15 < 10) 9/14/97 20 <=IF(A10-$I$25>=28,$I$24,IF(A10-$I$25>=21,$I$23,IF(A10-$I$25>=14,$I$22,IF(A10-$I$25>=7,$I$21,0)))) 20) Week 21) 1 $5 22) 2 $10 23) 3 $15 24) 4 $20 25) Start date 8/17/97MIKE

The results should be 0,5,10,15,20,20 if it is paid on the same day as the start date it still counts as week 1

It's how your measuring the time between dates. =IF(A5-$I$25>=21,$I$24,IF(A5-$I$25>=14,$I$23,IF(A5-$I$25>=7,$I$22,IF(A5-$I$25>=0,$I$21,0))))

MIKE

I dont get it? This gives me the correct answers. how did you do that?

I dont get it?

If you pay on the first day and through the 1st week you get charged 5If you pay the first day, which is 8/17/97,

then this is the same as your start date,

Since 8/17/97 minus your Start Date equals 0~~this makes your first week actually the numbers 0 through 7~~

this makes your first week actually the numbers 0 through 6

Date math is not easy and can get confusing.MIKE

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History