• 470K Members
• 5,147 Online
• 568K Conversations

New Contributor

# I need a cell to use a simple formula if date conditions are met

I am trying to plan for large expenses as equipment reaches it's lifespan.  I want to take a dynamic 15 year span, and if a:

date of purchase c3 plus expected lifespan i3 is more than a given year k3 to z3, I want o3 (in the case of the end of service date for row 3) to populate with a purchase price d3*e3 quantity owned to tell us we need to plan to budget in 2023 for \$31455.

I got conditional formatting to work for applying Red, yellow, green for due dates in the J column.

**I am aware of how that particular math will be flawed based on the spreadsheet given to me since it was \$31455 for 3 of them on the sheet, but I am dealing with what was handed to me and I will fix that math later, so please ignore that part if you see it in the picture.

8 Replies

# Re: I need a cell to use a simple formula if date conditions are met

I advise you to refrain from using conditional formatting as a basis of whether a condition exists. I will scrutinize your data in a little while to determine my suggested course of action you should take.

# Re: I need a cell to use a simple formula if date conditions are met

I understand you want a formula in cells K3 to Z3 that calculates a number if the year in row 2 is greater than the service date year plus lifespan. I don't see any number in column I in your screenshot, but I think the following formula should do what you describe. Put the formula into K3 and copy to the right.

=IF(YEAR(\$C3)+\$I3+1=K\$2,\$D3*\$E3,0)

# Re: I need a cell to use a simple formula if date conditions are met

@Twifoo Why? That is what conditional formatting is made for. Use formatting if a condition is true. That's the point of conditional formatting.

Highlighted

# Re: I need a cell to use a simple formula if date conditions are met

I personally believe that conditional formatting should be applied to reports, not on data, because such formatting can only accept a fixed range, never a dynamic range. Reports are limited to a certain range, and can therefore be conveniently applied with conditional formatting.
Conversely, data can extend to an indefinite range, thereby requiring a modification of the conditional formatting range, if and when such formatting is desired to be applied to the extended range.
With all due respect to you, I hereby stand ready to be corrected, if or when you can prove falsity hereof.

# Re: I need a cell to use a simple formula if date conditions are met

@Twifoo , IMHO, conditional formatting in many cases is clever enough to expand the range dynamically while you are adding the data. Apply it to entire column is also costs practically nothing.

# Re: I need a cell to use a simple formula if date conditions are met

Replacement of equipment occurs when it reaches the end of its service life, not after one year from such end; otherwise, no such equipment would be available for use within that one year period.
Premised on the foregoing, the formula in K3, copied to the right, is:
=((YEAR(\$C3)+\$I3)=K\$2)*(\$D3*\$E3)

# Re: I need a cell to use a simple formula if date conditions are met

Thank you all for your input.  After trial of both, I found the one that Twifoo provided to work.  Ineborg, yours worked also, but inserted the budget into the following year.

You are all wonderful.  Thank you for your assistance.

# Re: I need a cell to use a simple formula if date conditions are met

@InOverMyHead the formula I suggested puts the amount in the following year because of the +1 in the formula. Remove it if you want. It's how I understood the requirement.

Twifoo's formula is clever and shorter, but unless you really understand how it works, you may find it hard to maintain. An IF statement is a lot clearer than a multiplication with a comparison.  I prefer to keep it simple and easier to understand.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies