web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
Register Today: Microsoft Business Applications Update- April 15th
Your Invited to Keeping It Real with the Power Platform
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Using a calculated dat...
Power Apps
Answered

Using a calculated date column from SharePoint in a formula on Power Apps to show items expiring soon

(0) ShareShare
ReportReport
Posted on by Laela 21

Hi there, 

 

I have created a Calculated column on SharePoint List to calculate an Expiry Date for 4 years after a date which the user inputs. In the calculated column, I've used the following formula:

=DATE(YEAR([User Input Date])+4,MONTH([User Input Date]),DAY([User Input Date]))

 

Then, on Power Apps, I am attempting to show items that are expiring in the next 6 months by filtering the Expiry Date in a Gallery with the following formula:

Filter('Date source', 'Expiry Date' <= DateAdd( Today(), 180 ),!IsBlank('Expiry (Date'))

 

I am getting an error in the formula that says "Invalid argument type. Expecting a number value". 

The date is returning as a string and is returning in this format as an example:

yyyy-mm-ddT08:00:00Z

 

Please help me understand how to get the date in the correct format so that I can use it in a Power Apps formula (i.e. the Gallery formula described above).

 

Categories:
Building Power Apps
I have the same question (0)
  • WarrenBelz Profile Picture
    WarrenBelz 154,928 Most Valuable Professional on at

    Hi @Laela ,

    Happy to a explore a solution if you need to use your methodology, but I will make a suggestion first. If the source fields of the calculated column are available in PowerApps, why not do the lookups there?

     

    Please click Accept as solution 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 giving it Thumbs Up.

  • Laela Profile Picture
    Laela 21 on at

    Hi @WarrenBelz 

     

    Thank you for your reply. Please elaborate on what you meant by if the source fields are available in Power Apps?

     

    Just to give more context:

    • I have a Power Apps Edit Form on a screen where a user will input a date. An expiry date needs to be automatically calculated from that to be 4 years away from the user input date, and saved in my SharePoint List as an "Expiry Date". 
    • I have a gallery on a separate screen where "Expiry Date" data will be filtered to only show "Expiry Dates" that are 6 months away or less. The formula for this is not working though. 

     

    Regards,

    Laela

     

  • WarrenBelz Profile Picture
    WarrenBelz 154,928 Most Valuable Professional on at

    Thanks @Laela ,

    I am referring to the fact SharePoint Calculated Columns are based on other fields in the data set which PowerApps also has vision of so the calculation

    DATE(YEAR([User Input Date])+4,MONTH([User Input Date]),DAY([User Input Date]))

    could easily be done in Power Apps as 'User Input Date' is available so

    DateValue(
 Month(DateValue('User Input Date'.Text))
 & "/" & 
 Day(DateValue('User Input Date'.Text)) 
 & "/" & 
 Year(DateValue('User Input Date'.Text)) +4
)

    will get the calculated field value you did in SharePoint

     

    Please click Accept as solution 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 giving it Thumbs Up.

  • Verified answer
    Laela Profile Picture
    Laela 21 on at

    Hi @WarrenBelz 

     

    Thank you for your suggestion. 

     

    I managed to figure out a way to do it with the SharePoint calculated column though. 

    I did it this way just in case a user decides to update the User Input Date from the SharePoint List, because they will have access to edit the SharePoint List as well. 

     

    My Solution is:

     

    In my SharePoint List calculated column, I had the following formula to make the expiry date 4 years ahead of the User Input Date (I added the ISBLANK in case a user does not input a date):

    =IF(ISBLANK([User Input Date]),"",DATE(YEAR([User Input Date)])+4,MONTH([User Input Date]),DAY([User Input Date])))

     

    In my PowerApps Gallery, I have the formula to flag dates that are 6 months away from the expiry date:

    Filter('Data source', DateValue('Expiry Date') <= DateAdd( Today(), 180 )&&DateValue('Expiry Date',"en-GB") >= DateAdd( Today(), -180 ),!IsBlank('Expiry Date'))

    For anyone who is not familiar with it, here is the link to the DateValue page: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datevalue-timevalue

    "Converts a date, a time, or both in a string to a date/time value."

     

    Then, if I want to display the date in a label, I have the formula:

    Text(DateValue(ThisItem.'Expiry Date'),ShortDate)

     

    Regards, 

    Laela

  • WarrenBelz Profile Picture
    WarrenBelz 154,928 Most Valuable Professional on at

    Hi @Laela ,

    A little curious -  you do not need to delete the SharePoint column to do the direct PowerApps lookup - you just don't use it in PowerApps, however I am glad it worked for you.

  • lcdelgado Profile Picture
    lcdelgado 379 on at

    Hello!  I'm looking to do the same but for these date parameters 30,60,90,120,730 and 1095 days to expire, I have tried several formulas, but it doesn't work, if you can help me on this, I will really appreciate it. I've been trying to use these formulas: =[TODAY]()-[Expiration Date], =IF([Expiration Date]-120 <= NOW(),"Expired","Not Expired")
    =IF([Expiration Date]-[Today]>90;"Expired","Not Expired") 

  • WarrenBelz Profile Picture
    WarrenBelz 154,928 Most Valuable Professional on at

    Hi @lcdelgado ,

    Try this

    With(
 {
 wDate:
 DateAdd(
 Today(),
 -120,
 Days
 )
 },
 If(
 'Expiration Date' <= wDate,
 "Expired",
 "Not Expired"
 )
)

     

    Please click Accept as solution 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 giving it Thumbs Up.

    Visit my blog Practical Power Apps

  • lcdelgado Profile Picture
    lcdelgado 379 on at

    Thank you, Warren! it really helps me a lot! I have another question and it's about the concat formula! I have this formula for 30 days: 

     

    concat(concat('ExpirationDate',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('ExpirationDate',' lt ','''', outputs('x_Days_+_30'),'''')) 

     

    but when I tried to include 60 days it is giving me an error (Bad Request), this is the 60 days formula: 

     

    concat(concat(concat('ExpirationDate',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('ExpirationDate',' lt ','''', outputs('x_Days_+_30'),'''', 'ExpirationDate',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('ExpirationDate',' lt ','''', outputs('x_Days_+_60'),'''')))

    Can you try to help me here? thanks in advance

  • WarrenBelz Profile Picture
    WarrenBelz 154,928 Most Valuable Professional on at

    @lcdelgado ,

    Please post a new thread on this - I suggest on the Power Automate Community.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Good day.

     

    In my form i have a column with a date, in another column i would like a calculated date (120) days later than the other date. 

    I have tried a few formula's but it's not working

     

    Thanks for any and all help

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Register Today: Microsoft Business Applications Update- April 15th
Your Invited to Keeping It Real with the Power Platform

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the March Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 551

#2
WarrenBelz Profile Picture

WarrenBelz 430 Most Valuable Professional

#3
Valantis Profile Picture

Valantis 298

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans