Discussion Re: I need a cell to use a simple formula if date conditions are met in Excel
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463194#M29160
<P><LI-USER uid="322206"></LI-USER> </P>
<P> </P>
<P>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. </P>
<P> </P>
<P>=IF(YEAR($C3)+$I3+1=K$2,$D3*$E3,0)</P>
<P> </P>
<P> </P>Tue, 16 Apr 2019 21:02:50 GMTIngeborg Hawighorst2019-04-16T21:02:50ZI need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463067#M29158
<P>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:</P><P>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.</P><P>I got conditional formatting to work for applying Red, yellow, green for due dates in the J column.</P><P> </P><P> </P><P>**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.<span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="excel.jpg" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/109164i1DB820E23020ECD4/image-size/large?v=1.0&px=999" title="excel.jpg" alt="excel.jpg" /></span></P>Tue, 16 Apr 2019 20:43:28 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463067#M29158InOverMyHead2019-04-16T20:43:28ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463151#M29159
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.Tue, 16 Apr 2019 20:58:44 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463151#M29159Twifoo2019-04-16T20:58:44ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463194#M29160
<P><LI-USER uid="322206"></LI-USER> </P>
<P> </P>
<P>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. </P>
<P> </P>
<P>=IF(YEAR($C3)+$I3+1=K$2,$D3*$E3,0)</P>
<P> </P>
<P> </P>Tue, 16 Apr 2019 21:02:50 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463194#M29160Ingeborg Hawighorst2019-04-16T21:02:50ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463202#M29161
<P><LI-USER uid="280482"></LI-USER> Why? That is what conditional formatting is made for. Use formatting if a condition is true. That's the point of conditional formatting. </P>Tue, 16 Apr 2019 21:03:59 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463202#M29161Ingeborg Hawighorst2019-04-16T21:03:59ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463284#M29163
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.<BR />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.<BR />With all due respect to you, I hereby stand ready to be corrected, if or when you can prove falsity hereof.Tue, 16 Apr 2019 21:17:16 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463284#M29163Twifoo2019-04-16T21:17:16ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463483#M29167
<P><LI-USER uid="280482"></LI-USER> , 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.</P>Tue, 16 Apr 2019 22:14:45 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/463483#M29167Sergei Baklan2019-04-16T22:14:45ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/464251#M29175
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.<BR />Premised on the foregoing, the formula in K3, copied to the right, is:<BR />=((YEAR($C3)+$I3)=K$2)*($D3*$E3)Wed, 17 Apr 2019 05:19:20 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/464251#M29175Twifoo2019-04-17T05:19:20ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/472021#M29292
<P><LI-USER uid="280482"></LI-USER> <LI-USER uid="521"></LI-USER> <LI-USER uid="7724"></LI-USER> </P><P>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.</P><P> </P><P>You are all wonderful. Thank you for your assistance.</P>Thu, 18 Apr 2019 15:19:45 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/472021#M29292InOverMyHead2019-04-18T15:19:45ZRe: I need a cell to use a simple formula if date conditions are met
https://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/473366#M29318
<P><LI-USER uid="322206"></LI-USER> 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.</P>
<P> </P>
<P>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.</P>Thu, 18 Apr 2019 21:14:40 GMThttps://techcommunity.microsoft.com/t5/excel/i-need-a-cell-to-use-a-simple-formula-if-date-conditions-are-met/m-p/473366#M29318Ingeborg Hawighorst2019-04-18T21:14:40Z