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

ishpahuja

New Member
Joined
May 6, 2015
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]date[/td][td][/td][td=bgcolor:#70AD47]QF[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
31-May-19​
[/td][td][/td][td=bgcolor:#E2EFDA]Q2F19[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
30-Jun-19​
[/td][td][/td][td]Q2F19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
31-Jul-19​
[/td][td][/td][td=bgcolor:#E2EFDA]Q3F19[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
31-Oct-23​
[/td][td][/td][td]Q4F23[/td][/tr]
[/table]


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

Code:
[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]
 
Last edited:
Upvote 0
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?

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

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top