SUM with a Date (TODAY) formula

[leopardhawk]

I am using Excel 2010. I know that Excel can use 'TODAY' to calculate a formula but I need help.

I am trying to write a formula that will put a dollar amount in a cell AK142 based on the following criteria:

CELL AI142 - $366.00
CELL AJ142 - 1-Sep-11
CELL AK142 - ??

If todays date is before the date in AJ142, then the result in AK142 will be $0.00.
If todays date is equal to the date in AJ142, then the result in AK142 will be the dollar amount in AI142 minus the dollar amount in AI142 times .025.

I really hope this makes sense and that someone can help me. Thanks!

 

[T. Valko]

I am using Excel 2010. I know that Excel can use 'TODAY' to calculate a formula but I need help.

I am trying to write a formula that will put a dollar amount in a cell AK142 based on the following criteria:

CELL AI142 - $366.00
CELL AJ142 - 1-Sep-11
CELL AK142 - ??

If todays date is before the date in AJ142, then the result in AK142 will be $0.00.
If todays date is equal to the date in AJ142, then the result in AK142 will be the dollar amount in AI142 minus the dollar amount in AI142 times .025.

I really hope this makes sense and that someone can help me. Thanks!

What should happen if today's date is greater than the date in AJ142?

 

[tvanduzee]

Hello

Not sure if I am understanding it right, but are you attempting this in vba or on the worksheet?

CELL AI142 - $366.00
CELL AJ142 - 1-Sep-11
CELL AK142 - ??

If todays date is before the date in AJ142, then the result in AK142 will be $0.00.
If todays date is equal to the date in AJ142, then the result in AK142 will be the dollar amount in AI142 minus the dollar amount in AI142 times .025.

On the worksheet:<code>
<code></code></code><code><code>
</code></code><code>=IF(NOW()-AJ142<0,0,</code><code>AI142-(AI142*.025</code><code>))<aj142,0,ai142-(ai142*.025))></aj142,0,ai142-(ai142*.025))></code>
<code></code>
Be sure to format AK142 as being currency.
<code></code>
<code></code>This is assuming you have AJ142 Formatted as Date.
You did not mention what if todays date is greater than the date in AJ142 so I assumed that the value in AK142 would be the same as if it were equal.
<code><dte then=""></dte></code>
Hth
Terry

 

[leopardhawk]

Sorry, I meant to say that if if todays date is 'greater than or equal to the date in AJ142'. Is this what you were asking T. Valko?

 

[T. Valko]

Sorry, I meant to say that if if todays date is 'greater than or equal to the date in AJ142'. Is this what you were asking T. Valko?

Yes

Try this...

=IF(N(AJ142),IF(TODAY() < AJ142,0,AJ142*0.75),"")<AJ142,0,AJ142*0.75),"")< p>

 

[leopardhawk]

Hi Biff, I tried your formula and I received $30,590.25 as the result. Terry's formula returned a result of $356.85 which is the expected result. I'm not sure why yours didn't work but Terry's is working so perhaps I should just use his.

Is there a reason to choose one over the other (assuming that you are able to modify yours to return the expected result)?

Thanks to both of you for your help, I really appreciate it!

Mark

 

[T. Valko]

Hi Biff, I tried your formula and I received $30,590.25 as the result. Terry's formula returned a result of $356.85 which is the expected result. I'm not sure why yours didn't work but Terry's is working so perhaps I should just use his.

Is there a reason to choose one over the other (assuming that you are able to modify yours to return the expected result)?

Thanks to both of you for your help, I really appreciate it!

Mark

Ooops!

I misread your post. I saw this: .025, and in my mind I saw 0.25. That's quite a difference in the grand scheme of things!

That's why I wrote: AJ142*0.75

N-(N*0.25)

Is the same thing as:

N*0.75

So, my formula using the correct values would be:

=IF(N(AJ142),IF(AI141 < AJ142,0,AI142-(AI142*0.025)),"")<AJ142,0,AI142-(AI142*0.025)),"")< p>

 

[leopardhawk]

Hi Biff, sorry, your formula returns a result of $0.00 (I did change the date on my PC to a later date to test it) even after I fixed the typo in your formula (I'm pretty sure you didn't mean AI1410). I really can't see why but maybe you can.

Thanks for all your help.

 

[leopardhawk]

Sorry, I meant AI141...

 

[T. Valko]

Sorry, I meant AI141...

AI141 is the cell I used to hold today's date!

If you want the TODAY() function in the formula:

=IF(N(AJ142),IF(TODAY() < AJ142,0,AI142-(AI142*0.025)),"")