cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ljkeefe
Resolver I
Resolver 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
Resolver I
Resolver 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
Resolver I
Resolver 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 Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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