Partners filmexxx.link, sextotal.net, ahmedxnxx.com, https://xnxx1xvideo.com, russianxnxx

The FORMAT function is a gem when it comes to applying custom formats to your Calculations. To begin with I’ll share with you it’s bare syntax and then some fun ways you can use the FORMAT Function.

 

A Quick Video!

 

FORMAT Function Syntax

= FORMAT ( <value>, <format_string> )
  1. Value – Can be a Text or a Number
  2. Format String – Can be any of the various strings (available) to format the value in the desired way.

 

Quick Example, the following format function will result in 1000.4 (with one decimal), you get the point!

= FORMAT(1000.44, "0.0")

Now that we’ve slayed the Syntax, let’s play with a few interesting examples

 

Using Format with Numbers

The interesting part to watch out for is the strings used (highlighted in red) and the Result produced

Format Function Result What it Does
FORMAT(1000,"0,0")
1,000 Adds a comma separator as per your system settings
FORMAT(1000,"0,0.0")
1,000.0 Adds a commas and decimals
FORMAT(1000,"0.0;(0.0);-")
1000.0 Positive number – Appears in one decimal place
FORMAT(-1000,"0.0;(0.0);-")
(1000.0) Negative Number – Appears in brackets with one decimal
FORMAT(0,"0.0;(0.0);-")
Zero – appears as a dash
FORMAT(1000,"Currency")
$1,000.00 Appears in the Currency Format as per system settings
FORMAT(1000, "0 ✔️;(0) ❌")
FORMAT(-1000, "0 ✔️;(0) ❌")
1000 ✔️

(1000) ❌

Positive Number appears with a ✔️

Negative Number appears with ❌

 

Using Format with Dates

Again watch out for the strings used (highlighted in red) and the Result produced

Format Function Result What it Does
FORMAT(DATE(2020,1,20)+.20, "c")
1/20/2020 4:48:00 AM Display the date as ddddd and display the time as ttttt, in that order
FORMAT(DATE(2020,1,20), "dddd")
Monday Display the day as a full name (Sunday-Saturday)
FORMAT(DATE(2020,1,20),"w")
2 Display the day of the week as a number (1 for Sunday through 7 for Saturday)
FORMAT(DATE(2020,1,20), "ww")
4 Display the week of the year as a number (1-54)
FORMAT(DATE(2020,1,20), "mmmm")
January Display the month as a full month name (January-December)
FORMAT(DATE(2020,1,20), "q")
1 Display the quarter of the year as a number (1-4)
FORMAT(DATE(2020,1,20), "\Qq")
Q1 Prefixes a Q at the start of quarter number
FORMAT(DATE(2020,1,20), "yyyy \Qq")
2020 Q1 Displays Year and Quarter Number
FORMAT(DATE(2020,1,20), "ttttt")
12:00:00 AM Display a time as a complete time (including hour, minute, and second)

 

Using Format with Booleans

And one last time again watch out for the strings used (highlighted in red) and the Result produced

Format Function Result What it Does
FORMAT(0, "On/Off")
FORMAT(1, "On/Off")
Off

On

Displays Off if number is 0; otherwise, displays On
FORMAT(0, "True/False")
FORMAT(1, "True/False")
FALSE

TRUE

Displays False if number is 0; otherwise, displays True
FORMAT(0, "Yes/No")
FORMAT(1, "Yes/No")
No

Yes

Displays No if number is 0; otherwise, displays Yes

 

I am sure I have missed out a bunch of FORMAT function tricks that YOU know and I don’t. Be a gentleman and drop in your knowledge bomb in the comments. I’ll be so happy 😃

 

Learn DAX Functions

Check out my Video series for DAX Functions

 

More on Format from Microsoft

 

More on DAX

 

Tagged In .


Topics that I write about...