cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ljkeefe
Helper I
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.

ljkeefe_0-1653332341748.png

 

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({SecondOfMonthP:Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DayOfWeek2:2,N2:2},With({SecondMonday:DateAdd(SecondOfMonthP,(N2*7)-Weekday(DateAdd(SecondOfMonthP,7-DayOfWeek2)))},
With({ThirdOfMonthP:Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DayofWeek3:2,N3:3},With({ThirdMonday3:DateAdd(ThirdOfMonthP,(N3*7)-Weekday(DateAdd(ThirdOfMonthP,7-DayofWeek3)))},
With({FourthOfMonthP:Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DayofWeek4:2,N4:4},With({FourthMondayP:DateAdd(FourthOfMonthP,(N4*7)-Weekday(DateAdd(FourthOfMonthP,7-DayofWeek4)))}, 
With({PrevFourth: Date(Year('MeetingB'.SelectedDate),Month('MeetingB'.SelectedDate),1),DOW:-2,N:1}, 
With({PrevFourthMon:  DateAdd(DateAdd(PrevFourth,-1,Months),(N4*7) - Weekday(DateAdd(PrevFourth,7-DOW)))},
With({PrevThirdMon: DateAdd(DateAdd(PrevFourth,-1,Months),(N3*7) - Weekday(DateAdd(PrevFourth,7-DOW)))},


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
ljkeefe
Helper I
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.

View solution in original post

1 REPLY 1
ljkeefe
Helper I
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.

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.

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.

Users online (2,488)