Power Apps Exchange

Please login or click SIGN UP FOR FREE to create your PowerAppsUG account to join this user group.

View My Drafts

Back to discussions
Expand all | Collapse all

Add one day to selected date in "Date picker"

  • 1.  Add one day to selected date in "Date picker"

    Bronze Contributor
    Posted Jan 04, 2021 05:08 AM
    Hi All,

    I have two date pickers to select the From and To date. Based on these dates, i am trying to filter the data from sharepoint list.

    'DateTime Created'>= Fromdate.SelectedDate && 'DateTime Created'<= Todate.SelectedDate

    Challenge:

    Date looks like 1/1/2021 12:00:00AM and hence if a records has date like 1/1/2021 5:00:00AM, then it does not gets selected because "Time" is fixed as "12:00:00AM".

    I tried modifying formula as below, but then data is not getting filtered.

    'DateTime Created'>= Fromdate.SelectedDate && 'DateTime Created'<= (Todate.SelectedDate+1)

    Please advise.

    Thanks,
    Mohan

    ------------------------------
    Mohan Srinivasamurthy
    senior manager
    ------------------------------


  • 2.  RE: Add one day to selected date in "Date picker"

    Bronze Contributor
    Posted Jan 04, 2021 05:41 AM
    @Mohan Srinivasamurthy - Did you try

    Start Date: Text(DateTimeValue(Concatenate(Text(Fromdate.SelectedDate,"[$-en-US]mm/dd/yyyy")," 00:00:00")))
    End Date: Text(DateTimeValue(Concatenate(Text(Todate.SelectedDate,"[$-en-US]mm/dd/yyyy")," 23:59:59")))
     ​

    ------------------------------
    Shailendra Turlapati
    ------------------------------

    Original Message


  • 3.  RE: Add one day to selected date in "Date picker"

    Bronze Contributor
    Posted Jan 04, 2021 05:59 AM
    Hi @Shailendra Turlapati,

    Does it mean, i have to use it as below? I get an error on the below syntax.

    'DateTime Created'>= Text(DateTimeValue(Concatenate(Text(Fromdate.SelectedDate,"[$-en-US]mm/dd/yyyy")," 00:00:00"))) && 'DateTime Created'<= Text(DateTimeValue(Concatenate(Text(Todate.SelectedDate,"[$-en-US]mm/dd/yyyy")," 23:59:59")))

    I am also not clear why data is not getting filtered when i add +1 to selected data because in formula bar, i can see date is increased by 1. (I selected Dec 11th)

    ​
    Thanks,
    Mohan

    ------------------------------
    Mohan Srinivasamurthy
    senior manager
    ------------------------------

    Original Message


  • 4.  RE: Add one day to selected date in "Date picker"

    Top Contributor
    Posted Jan 05, 2021 01:24 AM
    Hi @Mohan Srinivasamurthy,
    The issue with comparing dates and date/time fields (as noted previously) is the the date has default of 0:00 in time, so the other alternative is to convert them all to dates 
    With(
   {
      wDate:
      DateValue(
         Text(
            'DateTime Created',
            "[$-en]dd/mm/yyyy"
         )
      )
   },
   wDate >= Fromdate.SelectedDate && 
   wDate <= Todate.SelectedDate
)​


    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------

    Original Message


  • 5.  RE: Add one day to selected date in "Date picker"

    Bronze Contributor
    Posted Jan 05, 2021 02:02 AM
    Edited by Mohan Srinivasamurthy Jan 05, 2021 02:12 AM
    Hi @Warren Belz,

    Thank you.

    I believe above statement converts 'DateTime Created' to date and removes time component. But, what i am trying is to remove default time component from "Date picker" or add one day to selected date in "Date Picker". Because, my output misses one day's of information.

    I did tried above code, but my combo box does not select any details and it is blank. I am using the code inside Filter and not sure if datetime is delegable function. Below is my complete code written on "Items" Property of Combo box.

    Sort(
    Filter(
    'Sharepoint Table',
    'Teamname'.Value = Dropdown1_1.Selected.Result,
    DateValue(Text('DateTime Created',"[$-en]dd/mm/yyyy"))>= Fromdate.SelectedDate &&
    DateValue(Text('DateTime Created',"[$-en]dd/mm/yyyy"))<= Todate.SelectedDate
    ),
    'Case Number',
    Descending
    )


    Please advise.

    Thanks,
    Mohan​​

    ------------------------------
    Mohan Srinivasamurthy
    senior manager
    ------------------------------

    Original Message


  • 6.  RE: Add one day to selected date in "Date picker"

    Top Contributor
    Posted Jan 05, 2021 06:19 AM

    Hi @Mohan Srinivasamurthy,
    I thought that was what I did - if they are both dates, they will have a time component of 0:00 and you should not need to add a day (which you do if you have time in one).
    Also, any date query (no matter how you frame it) is not Delegable, so that may be the issue with missing data​. Keeping with the With() statement for Delegation reasons (you might find this blog of mine useful) - doe this get you data

    With(
       {
          wDate:
          DateValue(
             Text('DateTime Created',"[$-en]dd/mm/yyyy")
          ),

          wList:
          Sort(
             'SharePoint Table',
             ID,
             Descending
          )
       },
       Sort(
          Filter(
             wList,
             'Teamname'.Value = Dropdown1_1.Selected.Result,
             wDate>= Fromdate.SelectedDate &&
             wDate<= Todate.SelectedDate
          ),
          'Case Number', 
          Descending
       )
    )



    ------------------------------
    Warren Belz
    Queensland Australia
    ------------------------------

    Original Message


  • 7.  RE: Add one day to selected date in "Date picker"

    Bronze Contributor
    Posted Jan 05, 2021 07:10 AM
    Hi @Warren Belz ​,

    Thanks for sharing your blog details,

    I get an error in the below line, says expected text or number -  "The function text has some invalid arguments".

    Text('DateTime Created',"[$-en]dd/mm/yyyy")

    Thanks,
    Mohan

    ------------------------------
    Mohan Srinivasamurthy
    senior manager
    ------------------------------

    Original Message