cancel
Showing results for
Did you mean:
Helper I

DateDiff Calculation issues

I hope to get some troubleshooting help. I've created a date calculator for users to calculate sequential meeting schedules and memo meeting deadlines.

• A quick summary of the calculator and meetings:
Meeting A is the first of the series and is every Monday, except the 5th Monday of the month.
• Meeting B is only on the first Tuesday of the month.
• Meeting C is optional
• Meeting D is on the first and third Tuesday of the month and is the final destination for a topic.

User feedback was that it would be more useful if users could calculate from the last meeting to the first (meeting A). The date is entered in the Meeting D date picker, it then calculates Meeting B (once a month), and finally Meeting A (every month except the 5th Monday).

The setup:

The deadline to attend a meeting is a week before the meeting. Therefore, there must be at least 6 days between the Meeting B deadline and the Meeting A meeting date. To avoid a circular reference issue that was coming up, I created a variable to calculate the number of days between the two dates in the OnChange property of the Meeting B deadline date picker.

``Set(varP, DateDiff( 'MeetingA'.SelectedDate, 'MeetingB'.SelectedDate,Days))``

The problem:

The date in the Meeting B date picker will be the first Tuesday of the month. Therefore, the date calculated for Meeting A will be either the fourth Monday of the month before Meeting B or the third Monday, depending on whether there are 6 or more days between the Meeting B deadline and the calculated Meeting A date.

However, the dates in my variable (varP) are not calculated correctly, therefore not following the logic of my IF statement (see below). As you can see below (red circles), there is one day between the Meeting B deadline and Meeting A meeting date, but when I go to the variable, varP, in my formula and hover, it shows 8 vs. 1.  According to my formula below, it should move my date to the third Monday of April. I know it calculated a Tuesday but I'm focusing on one issue at a time.

In the DefaultDate property of the Meeting A date picker, I have the following:

``````With({FirstMonth:Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DayOfWeek1:2,N1:1},With({FirstMonday:DateAdd(FirstMonth,(N1 * 7) - Weekday(DateAdd(FirstMonth,7-DayOfWeek1)))},

With({PrevFourth: Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DOW:-2,N:1},

If(IsBlank('MeetingB'),"",varP<=6,PrevThirdMon,PrevFourthMon))))))))))))``````

I am not a fan of all the With statements but it was the only solution I could find to help me get the nth day of whatever month. I used Time Leung's post: http://powerappsguide.com/blog/post/get-nth-weekday-for-a-month

Help is appreciated!

Thanks,

Linda

1 ACCEPTED SOLUTION

Accepted Solutions
Helper I

Well apparently my trigger is finally posting on this forum for help, and then solving it myself not long after. I realized that I placed my variable on the OnChange function of my deadline date picker which is in view only, which means OnChange won't work. I moved my variable to the OnSelect property of the meeting B date picker and it all calculates. Unfortunately, whenever my IF statement produces the 3rd Monday my date doesn't land on Monday but Tuesday. I suppose that's a post for another day.

Helper I

Well apparently my trigger is finally posting on this forum for help, and then solving it myself not long after. I realized that I placed my variable on the OnChange function of my deadline date picker which is in view only, which means OnChange won't work. I moved my variable to the OnSelect property of the meeting B date picker and it all calculates. Unfortunately, whenever my IF statement produces the 3rd Monday my date doesn't land on Monday but Tuesday. I suppose that's a post for another day.

Announcements

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,363)