cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Set calendar or date picker with recurring dates

I have searched all over for answers and I hope this forum can provide them. I've created meeting calculator for people to use when they are trying to present a topic to committee. One committee is on the 1st and 3rd Tuesday if the month, another is on the 2nd Tuesday of the month, another is every Monday, and the last one is every 2nd and 4th Tuesday of the month. The topic has to be presented at one meeting, if approved there, then it moves on to the next so on and so forth.

Having all these deadlines really confuses people so I am trying to create a calculator to help plan out their schedule and the deadlines for those memos. 

I've got the text boxes (in View mode only) set to calculate the deadline date from the initial date picker (there is one for each committee)

Here's what I'd like to do:

  1. For the initial date pickers: Is there a way that I can highlight the meeting frequency on that date picker. For example: Committee A is on the 1st & 3 Tuesday of the month, so those are highlighted on the date picker?
  2. Is it possible to have the date picker for Committee A determine the dates for the other Committees in chronological order?
  3. If this isn't possible with a date picker, is there another way? I saw an instruction video on how to do it with a text box.

I'm mostly interested in being able to write the code for the "1st & 3rd Tuesday of the month".

I am solely using the functions of PowerApps, there is no datasource like SharePoint. 

Any and all help is appreciated.

11 REPLIES 11
rosssimkins2013
New Member

I am also wanting to know how to add a recurring date selector.

WiZey
Super User
Super User

Hello @rosssimkins2013 ,

 

I tried this formulae based on Power App's basic calendar screen:

 

If(
    Or(
        DateAdd(
            _firstDayInView;
            ThisItem.Value
        ) = DateAdd(
            _firstDayOfMonth;
            3 - Weekday(_firstDayOfMonth) + If(
                Weekday(_firstDayOfMonth) > 3;
                7
            );
            Days
        );
        DateAdd(
            _firstDayInView;
            ThisItem.Value
        ) = DateAdd(
            _firstDayOfMonth;
            3 - Weekday(_firstDayOfMonth) + If(
                Weekday(_firstDayOfMonth) > 3;
                7
            ) + 14;
            Days
        )
    );
    RGBA(
        100;
        100;
        200;
        80%
    );
    RGBA(
        0;
        0;
        0;
        0
    )
)

 

And this is the result:

 

image.png

 

It is effectively highlighting the first and third tuesday of the current month. Is this what you were looking for?

 

Also, could you clarify a bit more your second point ? How do you expect a calendar to show events in a chronological order, when it is already set in a chronological order? 

rosssimkins2013
New Member

rosssimkins2013_0-1658311479557.png

So I have this so users can book a room and wanted the ability to use the drop down to make the booking every day, week, month or year. Then the until date is so it knows when they want to make the recurring booking till. For example every Tuesday from now till the end of the year. Thank you in advance.

WiZey
Super User
Super User

I've tried some formulae to calculate the repeat on days and weeks.

 

 

Clear(cltReservationDates);;
Switch(
    Dropdown1.SelectedText.Value;
    "Day";
    With(
        {
            NumberOfDays: DateDiff(
                _dateSelected;
                Self.SelectedDate
            )
        };
        Collect(
            cltReservationDates;
            ForAll(
                Sequence(NumberOfDays);
                {
                    ReservationDate: DateAdd(
                        _dateSelected;
                        Value;
                        Days
                    )
                }
            )
        )
    );
    "Week";
    With(
        {
            NumberOfDays: Trunc(
                DateDiff(
                    _dateSelected;
                    Self.SelectedDate
                ) / 7
            )
        };
        Collect(
            cltReservationDates;
            ForAll(
                Sequence(NumberOfDays);
                {
                    ReservationDate: DateAdd(
                        _dateSelected;
                        Value * 7;
                        Days
                    )
                }
            )
        )
    )
);;
//Remove week-end days from collection
RemoveIf(
    cltReservationDates;
    Weekday(ReservationDate) in [
        1;
        7
    ]
);;

 

 

This can create a collection of all days between a from date and a to date. 

 

"Days" return all days while "Week" return only the corresponding week day.

 

The "RemoveIf()" removes week-end days, but you can delete it if you want to keep saturday and sunday.

 

I'm still hitting my forehead to come up with the "Month" and the "Year" part, so hopefully someone already made something similar somewhere else.

 

Can you try this sample and see how it goes?

 

Edit:

 

I've completed the sample with month and year repetition. The formulae looks like this:

 

//Clear the collection of reservation dates
Clear(cltReservationDates);;

//Compute the date using the method for Day/Week/Month/Year repetition
Switch(
    Dropdown1.SelectedText.Value;
    "Day";
    //Daily repetition
    //NumberOfDays: day difference between FromDate and ToDate
    With(
        {
            NumberOfDays: DateDiff(
                _dateSelected;
                Self.SelectedDate
            )
        };
        //Add an iteration to create a list of dates between FromDate and ToDate
        Collect(
            cltReservationDates;
            ForAll(
                Sequence(NumberOfDays);
                {
                    ReservationDate: DateAdd(
                        _dateSelected;
                        Value;
                        Days
                    )
                }
            )
        )
    );
    //Weekly repetition
    //NumberOfDays: week difference between FromDate and ToDate
    "Week";
    With(
        {
            NumberOfDays: Trunc(
                DateDiff(
                    _dateSelected;
                    Self.SelectedDate
                ) / 7
            )
        };
        //Add an iteration to create a list of dates between FromDate and ToDate
        //The iteration is multiplied by 7 to jump from week to week.
        Collect(
            cltReservationDates;
            ForAll(
                Sequence(NumberOfDays);
                {
                    ReservationDate: DateAdd(
                        _dateSelected;
                        Value * 7;
                        Days
                    )
                }
            )
        )
    );
    //Monthly repetition
    //NumberOfDays:month difference between FromDate and ToDate
    "Month";
    With(
        {
            NumberOfDays: Trunc(
                DateDiff(
                    _dateSelected;
                    Self.SelectedDate
                ) / 7 / 4
            )
        };
        ForAll(
            Sequence(NumberOfDays);
            //ForwardDate:next iteration
            //NthWeekDayOfReservation:determine if week day is the first, second or third 
            //FirstDayOfForwardDate:first day of the next iteration
            With(
                {
                    ForwardDate: DateAdd(
                        _dateSelected;
                        Value;
                        Months
                    )
                };
                With(
                    {
                        NthWeekDayOfReservation: Trunc(
                            Day(
                                DateAdd(
                                    DateAdd(
                                        _dateSelected;
                                        1;
                                        Months
                                    );
                                    -1;
                                    Days
                                )
                            ) / 7
                        );
                        FirstDayOfForwardDate: Date(
                            Year(ForwardDate);
                            Month(ForwardDate);
                            1
                        )
                    };
                    //The calculation is done so we always get the nth week day of each iteration
                    Collect(
                        cltReservationDates;
                        {
                            ReservationDate: Date(
                                Year(ForwardDate);
                                Month(ForwardDate);
                                Weekday(_dateSelected) - Weekday(FirstDayOfForwardDate) + If(
                                    Weekday(FirstDayOfForwardDate) > Weekday(_dateSelected);
                                    7
                                ) + (7 * NthWeekDayOfReservation) + 1
                            )
                        }
                    )
                )
            )
        )
    );
    "Year";
    //Yearly repetition
    //NumberOfDays:year difference between FromDate and ToDate
    With(
        {
            NumberOfDays: Trunc(
                DateDiff(
                    _dateSelected;
                    Self.SelectedDate
                ) / 7 / 4 / 12
            )
        };
        ForAll(
            Sequence(NumberOfDays);
            //ForwardDate:next iteration
            //NthWeekDayOfReservation:determine if week day is the first, second or third 
            //FirstDayOfForwardDate:first day of the next iteration
            With(
                {
                    ForwardDate: DateAdd(
                        _dateSelected;
                        Value;
                        Years
                    )
                };
                With(
                    {
                        NthWeekDayOfReservation: Trunc(
                            Day(
                                DateAdd(
                                    DateAdd(
                                        _dateSelected;
                                        1;
                                        Months
                                    );
                                    -1;
                                    Days
                                )
                            ) / 7
                        );
                        FirstDayOfForwardDate: Date(
                            Year(
                                DateAdd(
                                    _dateSelected;
                                    Value;
                                    Years
                                )
                            );
                            Month(
                                DateAdd(
                                    _dateSelected;
                                    Value;
                                    Years
                                )
                            );
                            1
                        )
                    };
                    //The calculation is done so we always get the nth week day of each iteration
                    Collect(
                        cltReservationDates;
                        {
                            ReservationDate: Date(
                                Year(ForwardDate);
                                Month(ForwardDate);
                                Weekday(_dateSelected) - Weekday(FirstDayOfForwardDate) + If(
                                    Weekday(FirstDayOfForwardDate) > Weekday(_dateSelected);
                                    7
                                ) + (7 * NthWeekDayOfReservation) + 1
                            )
                        }
                    )
                )
            )
        )
    )
);;
//Remove week-end days from collection
RemoveIf(
    cltReservationDates;
    Weekday(ReservationDate) in [
        1;
        7
    ]
);;

 

This formulae should give you a list of dates between FromDate and ToDate, following the repetition on Day/Week/Month/Year.

 

Month and Year repetition is made to always give you the nth week day of the month. For example, if you selected the second Tuesday of the month, it will give you a list of all the second Tuesday of each months between FromDate and ToDate.

I tried other approaches, but I always ended up either skipping a month because the date ended up on a week-end or having two dates in the same month, so I believe this solution is the more optimal of the lot.

 

Can you try using the new formulae and see how it goes?

rosssimkins2013
New Member

rosssimkins2013_0-1658387083737.png

Thank you so much for all your work. It is so good of you. I have included a screen shot of the current formulae I have on the 'Continue' button. Is this where I would need to paste the formulae you have provided? The drop down with Day, Week, Month, Year in is called DDFrequency. Sorry to sound thick but I am just starting out my Powerapps journey. Thank you in advance.

WiZey
Super User
Super User

I have put my code in the "OnChange()" of a datepicker, so everytime I picked a new date it would compute a new list of dates. It was the earliest place where both "FromDate" and "ToDate" are defined.

 

I see you're defining "startTime" and "endTime" in your "OnSelect()" of the button. I'd say paste my formulae between the "Set()" and the "If()" and don't forget to change the variables' name I've used to adapt to your app's specifics (because "_dateSelected" is specific to my app).

 

You could even add another popup for when the "cltReservationDates" is empty to warn the user his selection doesn't bring up any result.

rosssimkins2013
New Member

Will try this now. Thank you.

rosssimkins2013
New Member

rosssimkins2013_0-1658479320535.png

I have added the code into where you said but am still getting a number of errors. Not sure what I am doing wrong now. Sorry and thank you for your help.

WiZey
Super User
Super User

Looks like it's mostly because I use semicolon as separator while you use comma. It's a difference of syntax between our country which should be easily fixable.

 

Can you try replacing all ";" with "," and see how it goes?

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,864)