MS Excel Resources

Formulas

Federal Government Fiscal Year

Fiscal Year

Convert a regular calendar date to the Federal Fiscal Year, where the calendar date is in cell "B2"

="FY"&YEAR(DATE(YEAR(B2),MONTH(B2)+3,1))

Example Output: FY2022 

Fiscal Quarter

Convert a regular calendar date to the Federal Fiscal Year, where the calendar date is in cell "B2"

="FY"&YEAR(DATE(YEAR(B2),MONTH(B2)+3,1))

Example Output: FY2022! 

Dollar Value Formatting

A nice custom number format that handles thousands, millions, and billions

[<999950]$0.0,"K";[<999950000]$0.0,,"M";$0.0,,,"B"

Example Output: $2.3K      $4.5M       $9.2B

Use Cases

New Vendor Discovery

Create a column with a list of UEIDs from one Fiscal Year, then a new column with UEIDs for another Fiscal Year. Use the below filtering formula to find values unique to the newer Fiscal Year list. In this example, FY22 UEIDs are in the range A2:A16, and FY23 UEIDs are in B2:B16. H/t to TikTok account @xecutethevision.com

=FILTER(B2:B16,NOT(COUNTIF(A2:A16,B2:B16)))

Example range and output