DropDown with YEARS and fixed time periode

(0) Share

Report

Posted on by MD2024

261

Hello

I have a SharePoint list with a DATE field and Title field.

I have a fixed time periode (fiscale year) from in a year XXXX based on a drop down:

DropDown: Item ["2023","2024","2025"]

The periode is like:

> 30.09.XXXX And < 01.10.XXXX (year plus 1) //EXAMPLE: > 30.09.2024 And < 01.10.2025

So I have created the following formula for a Gallery to show only items in that period:

/*
myDate is the SP-Column in the list TEST_DATE and 
the date has the format: dd.mm.yyyy which is set in DataCardValue FORMAT
I added a required year  Text(Dropdown1.Selected.Value +1) 
*/

Filter(
    TEST_DATE,
    Dropdown1.Selected.Value in myDATE > DateTimeValue("10.09." & Text(Dropdown1.Selected.Value) And Dropdown1.Selected.Value in myDATE < DateTimeValue("31.03." & Text(Dropdown1.Selected.Value +1)))
)

I can remeber that there is an issue with the standard format for DATE in SharePoint mm.dd.yyyy but we need to use for our user dd.mm.yyyy.

The error in the gallery is: "The value 'false' cannot be converted to date or time value".

Looks like that the DateTimeValue function is not converting it correct...

Any ideas are appreciated :)

Thanks in advance.

Kind regards

Michael

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Suggested answer

Inogic 1,135 Super User 2025 Season 2 on at

Like (0)

Report
Hi,

It seems you're having trouble with the date format and the logic of filtering your SharePoint list based on a custom fiscal year period in Power Apps. Specifically, you're using the DateTimeValue function, which may not handle your dd/mm/yyyy format properly.
Here’s how we can address your issue:
Key Points:

SharePoint stores dates in the mm/dd/yyyy format (US standard), but Power Apps will interpret this based on the user's locale settings.

You need to ensure the myDATE column is interpreted correctly in the comparison.

The logical structure for filtering a range should be updated slightly.

Suggested Formula
You can use the following approach:
Filter(
    TEST_DATE,
    DateTimeValue(myDATE) > DateValue("30.09." & Text(Dropdown1.Selected.Value)) &&
    DateTimeValue(myDATE) < DateValue("01.10." & Text(Dropdown1.Selected.Value + 1))
)
Explanation:

DateTimeValue: Converts the myDATE from SharePoint into a date/time value that can be compared.

DateValue: Converts the hard-coded string like "30.09.2024" or "01.10.2025" into date values for comparison.

Logical Operators: We use && (AND operator) for the condition to check both the start and end dates for the fiscal year range.

Handling Date Format:

Input Date (myDATE): Ensure that the myDATE column in your SharePoint list is properly formatted and stored. You don't need to worry about the exact display format (dd.mm.yyyy), as Power Apps will interpret it internally as a date object.

If you still have issues with date formats, ensure that your myDATE column is treated as a Date/Time column in SharePoint (and not a string).

Hope this helps.

Thanks!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Was this reply helpful? Yes No
Verified answer

MD2024 261 on at

Like (0)

Report

Thanks to Inogic

The Problem was that format in SharePoint list (US) mm/dd/yyyyy I found the solution but you are right, thanks for that.

So I realized to use that format like the following which is US:

Filter(

TEST_DATE,

DateValue(Text("9/1/"& Dropdown1.SelectedText.Value)) < myDATE &&

myDATE < DateValue(Text("10/1/"& Dropdown1.SelectedText.Value + 1))

)

After that I need to change the format in other fields to dd.mm.yyyy for the users to hold it simple.
It looks like the internal SharePoint format ist US.

Was this reply helpful? Yes No
Inogic 1,135 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Glad to hear your issue has been resolved! Could you please verify the answer if it helped? This will make it easier for others to find the solution as well.

Thanks!

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Was this reply helpful? Yes No