Answered

SharePoint calculated Column Value couldn't be formatted using Format Number

(3) Share

Report

Posted on by SO-06011624-0

I'm having an Calculated Colum "ExpiringIn" in SharePoint list which return values as Numbers with 0 decimals. When I'm trying to pass this value on an email content, it's becoming 2.00000000000000, instead of just the integer value. To format this number, I have used the expression on the flow as "formatNumber(item()?['ExpiringIn'],'D2')".

Still getting error as the value is null.

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Suggested answer

Vish WR 2,346 on at

Like (2)

Report
Copy link

Link copied!

@SO-06011624-0

First, the column name ExpiresIn in item()?['ExpiresIn'] may not match the actual internal name SharePoint uses. Check the real internal name from List Settings and update it.

The format string 'D2' does not work on decimal values and SharePoint calculated columns always return floats regardless of your decimal setting. Switch to '0' instead.

formatNumber(coalesce(item()?['ExpiresIn'], 0), '0')

The coalesce handles the null, and '0' strips the decimals cleanly.

Vishnu WR

LinkedIn - My Tech Space

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥

Was this reply helpful? Yes No
Expiscornovus 33,874 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @SO-06011624-0,

Have you double checked that you used the correct internal field name?

In the screenshot of your interface the name is Expiring In (not Expires In). Unless the internal name of the field is different?

Otherwise try:

item()?['ExpiringIn']

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
CU28041150-1 4 on at

Like (1)

Report
Copy link

Link copied!

Can try the Formula change :

formatNumber(int(item()?['ExpiresIn']), 'D2')

Was this reply helpful? Yes No
Suggested answer

Valantis 4,902 on at

Like (1)

Report
Copy link

Link copied!

Hi @SO-06011624-0,

The error is happening because some rows have a null value in ExpiresIn — you can see in your data that the second row has no License Number, which means the calculated column has nothing to calculate and returns null. formatNumber crashes on null.
Wrap it in a coalesce to handle the null:
formatNumber(coalesce(item()?['ExpiresIn'], 0), 'D2')
This substitutes 0 when the value is null so the flow doesn't fail. Change the 0 to whatever default makes sense for your scenario.
Also worth noting: 'D2' formats as a 2-digit integer with leading zero (e.g. 02, 59). If you just want a plain integer with no leading zero, use '0' instead:
formatNumber(coalesce(item()?['ExpiresIn'], 0), '0')

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
Suggested answer

11manish 2,151 on at

Like (0)

Report
Copy link

Link copied!

The issue occurs because SharePoint calculated columns return values as strings, not numbers, and sometimes return null if not yet evaluated.

The formatNumber function fails because it expects a numeric value and does not support the 'D2' format.

The correct approach is to first handle null using coalesce(), convert the value to a number using float(), and then format it. For example:

formatNumber(float(coalesce(item()?['ExpiresIn'], 0)), '0') will return a clean integer without decimals.

Was this reply helpful? Yes No
Suggested answer

Haque 2,567 on at

Like (2)

Report
Copy link

Link copied!
Hi @SO-06011624-0,

The error and formatting issue you’re encountering happens because the value passed to formatNumber() is sometimes null, and also because the 'D2' format specifier expects an integer but your value might be null or decimal.

How to fix this:

Handle null values before formatting: Use a conditional expression to check if the value is null and provide a default (like 0) before formatting.

Use the correct format specifier: 'D2' is for integers with at least 2 digits (e.g., 02, 15). If you want to format as a number with decimals, use 'N0' for no decimals or 'F0' for fixed-point with zero decimals.

This expression will help:

formatNumber(coalesce(item()?['ExpiresIn'], 0), 'N0')

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
SO-06011624-0 18 on at

Like (1)

Report
Copy link

Link copied!

I have reconfirmed the internal name of the calcu;ated column - "ExpiringIn"..

Also tried implementing the last 3 answers with no luck. Please find the attached error screenshot .

Was this reply helpful? Yes No
Suggested answer

Haque 2,567 on at

Like (2)

Report
Copy link

Link copied!

Hi @SO-06011624-0,

Let's narrow down, can you please see what the output of this expression coalesce(item()?['ExpiringIn'], 0)? Request you to pose the output of this expression.

If you still get the error, it might be because the value is missing or the path item()?['ExpiresIn'] is incorrect in your flow context. Double-check that the dynamic content is correctly referenced.

By the way - is the flow context the value is coming from the body() of a previous action rather than from the current item() in an iteration? If so, can you please try: coalesce(body()?['ExpiringIn'], 0)

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
SO-06011624-0 18 on at

Like (0)

Report
Copy link

Link copied!

Tried this coalesce(item()?['ExpiringIn'], 0) and getting this same error.

Was this reply helpful? Yes No
Verified answer

Haque 2,567 on at

Like (2)

Report
Copy link

Link copied!
Hi @SO-06011624-0,

I understand its giving you a real trouble!

Just to narrow down and for a trial-check could you please check how these expressions behave?

formatNumber(int(coalesce(item()?['ExpiringIn'], 0)), 'D2')

Or

formatNumber(float(coalesce(item()?['ExpiringIn'], 0)), 'N2')

Would be glad if you please post me two resutls. Please let me know how these two expression behaves.

Was this reply helpful? Yes No