Nesting IF and DATEDIF

[Ken Russell]

I have a spreadsheet with the following cells.

Cell B23= Purchase Date
Cell $E$7 = Today's date
Cell F23 = Purchase Price
Cell F24 formula needed as described below

I need to write a formula that compares the two dates and does the following.

If the Purchase date B23 is after Today's date $E$7 then show result as the Purchase price as a negative value.

If the Purchase date B23 is Today's day's date $E$7 then show result as the Purchase price as a negative value.

If the Purchase date B23 is before Today's day's date $E$7 then show the number of days difference times the Purchase price E23 as a negative value.

Any help would be greatly appreciated.

 

[MARZIOTULLIO]

Hi

=IF(OR(B23>TODAY(),B23=TODAY()),-F23,B23-TODAY()) in F24

 

[ukmikeb]

hi

Try :-

=-F23*IF(B23>=$E$7,1,$E$7-B23)

or

=-F23*IF(B23=>$E$7,1,DATEDIF(B23,$E$7,"d"))

hth

 

[Ken Russell]

OOPS! I left out a critical requirement.

If the Purchase date B23 is after Today's date $E$7 then the value in F24 should be the 365 times the Purchase price F23 minus the difference in the number of days.

Sorry abut that.

Cheers.