cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rvdhorst
Helper II
Helper II

Start and end date and then jump

I have a start and end date.

Say start date is January 1 and end date is January 31.
How do I manage to get a list that shows .

January 1st
January 2nd
January 14
January 15
January 28
January 29

etc.

 

So the date should be 2 days each time and then jump 14 days. The end date can go on until, for example, the end of May.

This must appear in an edit screen (so it must first be visible) and then it must be written into a sql table after pressing an ok button.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@rvdhorst 

Your example of days does not match your request...

There are 13 days between Jan 1 and Jan 14.  Yet, there are 14 days between Jan 14 and Jan 28!

So the "jump" of days is not the same.

 

An example formula that would provide a consistent jump of 14 days with a start and end range would be the following:

With({_start: Date(2021,1,1), _end:Date(2021,5,31), _jumpDays:14},
    Ungroup(
        ForAll(Sequence(DateDiff(_start, _end, Days), 1, _jumpDays),
            Table({Date: DateAdd(_start, Value-1, Days)}, {Date: DateAdd(_start, Value, Days)})
        ),
        "Value"
    )
)

This will produce a table with a single column called Date.  The Dates would be as you wanted based on your description - but not your example dates because of the discrepancy.

RandyHayes_0-1639756995511.png

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes
Super User
Super User

@rvdhorst 

Just replace that in the formula to the yourDatePicker.SelectedDate.

Ex.

With({_start: DatePickerStart.SelectedDate, _end: DatePickerEnd.SelectedDate, _jumpDays:14},
    Ungroup(
        ForAll(Sequence(DateDiff(_start, _end, Days), 1, _jumpDays),
            Table({Date: DateAdd(_start, Value-1, Days)}, {Date: DateAdd(_start, Value, Days)})
        ),
        "Value"
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

10 REPLIES 10
RandyHayes
Super User
Super User

@rvdhorst 

Your example of days does not match your request...

There are 13 days between Jan 1 and Jan 14.  Yet, there are 14 days between Jan 14 and Jan 28!

So the "jump" of days is not the same.

 

An example formula that would provide a consistent jump of 14 days with a start and end range would be the following:

With({_start: Date(2021,1,1), _end:Date(2021,5,31), _jumpDays:14},
    Ungroup(
        ForAll(Sequence(DateDiff(_start, _end, Days), 1, _jumpDays),
            Table({Date: DateAdd(_start, Value-1, Days)}, {Date: DateAdd(_start, Value, Days)})
        ),
        "Value"
    )
)

This will produce a table with a single column called Date.  The Dates would be as you wanted based on your description - but not your example dates because of the discrepancy.

RandyHayes_0-1639756995511.png

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi Randy,

Thank you very much for your quick reply.

 

Where should I put this code? If I place this under an onselect under a button, it does not work unfortunately.

How do I get this working in a SQL database? What is the variable I need to write?


Raymond

 

RandyHayes
Super User
Super User

@rvdhorst 

The Formula provided can be used as an Items property of any control that has an Items property...like a Gallery.  It's not based on any OnSelect or variables, it is a PowerApp formula for a table.

 

As far as what you want to do with that next (i.e. in relation to SQL) is not known as you didn't mention what you really wanted to submit.  You mentioned an Edit screen - is that to say there is some EditForm?  And then writing to a SQL table...but what?  Provide some more detail on your SQL table and I can expand a little further.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks Randy,

This indeed works.

 

What I would like if someone enters a start and end date via a Datepicker, then the gallery will be shown. If the result/value is approved, the date will be written into a SQL Table by an ok button in a table test table, the record is called Date) So in the table test table there is a record Date.

 

Raymond

RandyHayes
Super User
Super User

@rvdhorst 

So then on your approve/okay button, the OnSelect would use the Gallery as the datasource to create new records.

The formula would be:

Collect(yourSQLtableDataSource,
    ForAll(yourGallery.AllItems,
        {Date: Date}
    )
)

Again, assuming that the column name in your SQL table is Date, then this will create x number of records in your table corresponding to the gallery items.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks Randy,

How can I start working with a Datepicker and not with fixed days?

 

Raymond

RandyHayes
Super User
Super User

@rvdhorst 

Just replace that in the formula to the yourDatePicker.SelectedDate.

Ex.

With({_start: DatePickerStart.SelectedDate, _end: DatePickerEnd.SelectedDate, _jumpDays:14},
    Ungroup(
        ForAll(Sequence(DateDiff(_start, _end, Days), 1, _jumpDays),
            Table({Date: DateAdd(_start, Value-1, Days)}, {Date: DateAdd(_start, Value, Days)})
        ),
        "Value"
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thank you very much Randy

I'll get started on this tomorrow. Can I still ask if I have any questions? I'll mark it as resolved. A very nice day.

Raymond

RandyHayes
Super User
Super User

@rvdhorst 

Sounds good.  Yes, feel free to ask if you run into issues.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,458)