using a specific date in a function type field

(0) Share

Report

Posted on by HansHeintz

704

Hi,

I would like to have a function field where I convert an existing datetimefield to an integer f.e. representing the old fashioned days since the beginning of the 20th century.

I can see I can use the datediff function in a function field type but not the datevalue function. So I see no way to actually enter a date like "01-01-1900" into the function.

Also if i would like to convert the date to text in a function type field I have no options. Text() only alows numbers in this context no dates.

Is de function field type still in it's child-shoes or am I missing things?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
Hi @HansHeintz ,

To get a fixed date, you can use the Date() function:

Date(1900,1,1)

You can then convert this to a text using the Text() function:

Was this reply helpful? Yes No
HansHeintz 704 on at

Like (0)

Report

Sorry the Date function is not valid in the syntax for a formula field type.

Was this reply helpful? Yes No
HansHeintz 704 on at

Like (0)

Report

What I can use is the Now() function but this seems a bit silly since what is now now will not be now when I look at the data and I guess the value for this field is hard coded into the table everytime a change to the record is made.

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

Hi @HansHeintz ,

Are you talking about a Formula type column in Dataverse? Since you posted this in the Power Apps community on the Building Power Apps forum, I assumed this was about Power Apps.

In case you are talking about Dataverse, then you are right: Dataverse formula columns are not in General Availability yet, but in preview so functionality is limited: Work with Dataverse formula columns - Power Apps | Microsoft Learn

Eventually these formula type columns are supposed to replace calculated columns, but those don't support what you are looking for either: Define calculated fields | Microsoft Learn

The workaround I've been exploring is to add a another column to the table:

Using a business rule this is automatically set to 1/1/1900 upon creation of a new row:

This column can now be used in the Formula column (or calculated column) to act as the reference:

Was this reply helpful? Yes No