Log In
Register
Home
May 8, 2018
Offline
I would appreciate if anyone or Mynda herself can please assist.
Two weeks ago Mynda posted a video on YouTube explaining "Convert Dates to Fiscal Periods".
Our financial year also runs from Jul-June, so say all the dates in Column A are Delivery Dates e.g. 15/03/2019; 03/07/2019; 15/12/2020, etc. I know now how to extract the Fiscal Years (FY) for these dates thanks to Mynda's video. However, I want to join the following to Mynda's formula in Column B but struggle to get it right:
If the Delivery Date is greater than or equal to Today's Date, then return the text string "Future" otherwise if the Delivery Date is any date prior to Today's Date then return the text string "Overdue in FY????" e.g.
15/03/2019 to be "Overdue in FY2019"
03/07/2019 to be "Overdue in FY2020"
15/12/2020 to be "Overdue in FY2021";etc.
All within Mynda's formula where the fiscal year is extracted.
Thanks
See Answer
July 16, 2010
Offline
Hi Amien,
You can try this formula where your delivery dates are in the 'Date' column of an Excel Table:
=IF([@Date]<TODAY(),"Overdue in ","Future ")&IF(MONTH([@Date])<7,YEAR([@Date]),YEAR([@Date])+1)
Mynda
Answers Post
May 8, 2018
Offline
Wow! Works perfect. Mynda, you are awesome as always.
I notice the ConCat in there.
Thanks and much apreciated.
May 8, 2018
Offline
Hi Mynda
I wanna take this a step further. If my table/data (without any formulas) is imported into Power Pivot, do I add a Measure to get to the same result as per your solution in the normal table and how do I write the formula in the Measure? OR if a Measure is not workable how do I write the same formula (your solution) in the first blank column of the table in Power Pivot?
Whether it is a Measure or an add on column with the formula, I wanna use it in a Pivot Table to build my interactive Dashboard based on the Fiscal Year in which the Delivery Dates are Overdue or Future.
Hope you can refer to my initial question for the Data and help with this.
1 Guest(s)
