Display Text different than actual value (Dynamic and big set of data)

[ishpahuja]

Hi friends

I have the following data with me:
31-May-2019
30-June-2019
31-Jul-2019
........
........
31-Oct-2023

While I would need these dates as values (for my other formulas in sheet), I want to "display" Quarter and Financial year number against selected dates on the Dashboard. For e.g. 31-May-2019 will have Q3F19 while 31-Oct-2020 will have Q4F20.
This will only be the 'Displayed text' while the actual value remains as Date, as my other calculations are using Date.

How can I get different displayed text than actual values, considering I have so many dates. Thanks in anticipation.

Cheers

 

[sandy666]

like this?

date		QF
31-May-19​		Q2F19
30-Jun-19​		Q2F19
31-Jul-19​		Q3F19
31-Oct-23​		Q4F23

if you are able to use PowerQuery (Get&Transform)

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Quarter = Table.AddColumn(Source, "Quarter", each Date.QuarterOfYear([date]), Int64.Type),
    Year = Table.AddColumn(Quarter, "Year", each Date.Year([date]), Int64.Type),
    Extract = Table.TransformColumns(Year, {{"Year", each Text.End(Text.From(_, "en-GB"), 2), type text}}),
    QPrefix = Table.TransformColumns(Extract, {{"Quarter", each "Q" & Text.From(_, "en-GB"), type text}}),
    YPrefix = Table.TransformColumns(QPrefix, {{"Year", each "F" & Text.From(_, "en-GB"), type text}}),
    Merge = Table.CombineColumns(YPrefix,{"Quarter", "Year"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"QF"),
    ROC = Table.SelectColumns(Merge,{"QF"})
in
    ROC[/SIZE]

 

[kweaver]

Isn't May in the 2nd quarter? Then, 31-May-2019 would be Q2F19 ?

With date in question in the I3 cell (or adjust the column, then fill down as needed), does this do it?

="Q"&ROUNDUP(MONTH(I3)/3,0)&"F"&TEXT(I3,"yy")