Calculated column to compare date with today date.

(0) Share

Report

Posted on by daydinho10

124

I am moving my data source from sharepoint to datavers. I have date columns that i want to compare to generate status.

I have two columns [Due to Customers] and [Date Submitted]. Basically I just want to create a column that show the submission status base on the two date columns. Below is the code that works when I use it in sharepoint.

=IF(AND([Date Submitted]="",[Due to Customers]>TODAY()),"Due Later",IF(AND([Date Submitted]="",[Due to Customers]<=TODAY()),"Over Due",IF([Date Submitted]>[Due to Customers],"Late Submitted",IF([Date Submitted]<=[Due to Customers],"On Time Submitted",IF([Date Submitted]<[Due to Customers],"Over Due")))))

It seem dataverse fomular work a litle bit different and I strugle in comparing due date to today date. I try to use UTCtoday but it can't compare as it is in different timezone format. I tries switch funtion but still not success full. Can some body write the fomular base on below condition.

If [Date submitted] is blank and [Due date to customer] is more than today then "Due later"

If [Date submitted] is blank and [Due date to customer] is less than or equal to today then "Over due"

If [Date submitted] more than [Due date to customer] then "Late Submitted"

If [Date submitted] Less than or equal to [Due date to customer] then "On Time Submitted"

Else "Over due"

appriciate your help

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report

Hi @daydinho10,

The new formula column type has some current limitations when it comes to comparing/converting between UTC to local time. Hopefully this gets addressed soon.

You can use the older calculated column type to do this comparison and use the Now() function. Note that you might need to define an extra calculated column that is just equal to Now(), and then compare your dates to that column as part of the calculation.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
daydinho10 124 on at

Like (0)

Report

Thank for confirming, I think the calculated column would be the only choice at the moment

Was this reply helpful? Yes No
daydinho10 124 on at

Like (0)

Report

I test it and it turn out that it doesn't work. I can't set the condition to compare with today date as there is an option set to select instead of the fomular box. the option allow compare with value but can't use formula. the work around I teste is to create a new column with Now() funtion but the problem is the date won't change unlease record is updated or created.

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report

Hi @daydinho10,

Yes as I mentioned you'd need the extra column that is just equal to Now(). Also, calculated columns are computed on retrieval of the record, because its not actually stored in the database. So refreshing the form will cause the record to be retrieved and show the most up to date value. The form also refreshes if you create or update the record.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
daydinho10 124 on at

Like (0)

Report

@ChrisPiasecki Yes, you are right. I decided to stay with sharepoint list

Was this reply helpful? Yes No