Customer experience | Sales, Customer Insights,...

Formula field to calculate the difference in days between two date only fields.

(2) Share

Report

Posted on by MF-29051904-0

Hi!

I want to show the age of open deals in Opportunity and the time needed to close Won deals.

Trying to create formula fields for this, I'm discovering the DateDiff and many other date functions are not supported in this context.

Any ideas on how to achieve this?

Thanks.

Categories:

Dynamics 365 Sales

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

11manish 365 on at

Like (1)

Report
Copy link

Link copied!
Power Fx formula columns in Dataverse currently have limited support for certain date functions, including DateDiff.
To get around this for Opportunities, you should use the Calculated Column feature instead of the "Formula" (Power Fx) column type. Calculated columns support the DiffInDays function, which is exactly what you need.

Why use this over Power Fx?

Support: DIFFINDAYS is a native legacy function that works reliably for "Date Only" and "User Local" behaviors.

Real-time: It updates whenever the record is retrieved or saved.

Simplicity: It handles the math without needing complex workarounds for the missing DateDiff function in the newer Power Fx editor.

Was this reply helpful? Yes No
Suggested answer

Jimmy Passeti 849 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hello,

You can use the DateDiff function, but I don't think you'll be able to get what you need because the fields have different formats.

'Created On',

Regards,
Jimmy Passeti | Microsoft MVP
✅ Please mark as Verified if this answered your question!

Was this reply helpful? Yes No
Suggested answer

ManoVerse 935 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Use a Formula Column with simple date math as there are still some functions are unsupported there:

like If(Status = "Open", Today() - CreatedOn, Blank()) Date subtraction works when DateDiff is limited for this column.

how you can implement this for your scenario:

1. Create a custom field: new_closeactualdate

2. Set it via Power Automate when Opportunity becomes Won , required because Dataverse doesn’t store a usable close date for calculation on opportunity record.

3. Use a Formula Column:
If(Status = "Won", new_closeactualdate - CreatedOn, Blank())

*Use Power Automate, as recommended by Microsoft when formula limitations block date logic

*Calculated columns are not future proof as Microsoft is deprecating them gradually

Was this reply helpful? Yes No