Date formula, =

[srizki]

'=IF(H9="","",IF(H9="88500100","1217 ADV",IF(I9="BIL",LEFT($U$8,4)&" JIB",)))

In column U, I have the above formula, I want to amend the second condition, where it says if H9 is 88500100 then enter 1217 ADV.
As you see the 1217 is hard codded, because for January to June of 2018 it will be 2017 ADV, from July to December 2018 it will be
1218 ADV. I would like the formula to change it automatically.
Thanks,

 

[Gerald Higgins]

How is the relevant date identified ?

Can we do it using a TODAY() function or similar ?
Or is the relevant date held in a cell somewhere ?

 

[srizki]

Hi Gerald,

Yes that is what I am looking for, Today() is appropriate, something like, =IF(TODAY()<=DATE(2018,6,30),"1217 ADV","1218 ADV")
I want to add this in the formula.

Thanks,

 

[Gerald Higgins]

OK, what would the solution look like for other years ?

 

[srizki]

Actually it is ad valorem tax, so we for the years we put end of the year date, and the accounting code for the tax is 88500100. so I was told that for the first 6 months we go back to the prior years and the next 6 months we forward the date so it will be the end of current year. The same would be for all years.

 

[Gerald Higgins]

OK, that doesn't really help me. Don't know what ad valorem tax is, just looked it up and I think I get the basics, but can't see how it's relevant to this.

Same question again . . . .

OK, what would the solution look like for other years ?

 

[srizki]

You are right, all in need is to change the date to last month and year of prior year if the current month is withing first 6 months or change to last month of the year if the current month is withing the last six months of the year.
So, if it is Jan, Feb, Mar, Apr, May or Jun, it should be 1217 ADV(assuming it is in 2018), or 1218 ADV, if the current month is one of Jul, Aug, Sep, Oct, Nov or Dec.
I hope I made it clear.
Best regards,