Solved: DATEDIFF variable not calculating correctly

ljkeefe · ‎05-26-2022

Just when I solve one issue with this app, I discover another.

I created an app with several date pickers that calculate dates in reverse. I had to create a variable for the final date picker because its date would be contingent on its own date and the deadline of the meeting before that, thus would give me a circular reference error.

The problem is that the variable calculates the days in between the deadline and the meeting date correctly but when I check the variable in the IF statement of the date picker I need the date to generate on, the days are wrong.

The example below shows a 1 day difference between the two circled dates, which shows correctly in the Set function.

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

But shows a much larger number in the IF statement of Meeting A date picker. If I highlight varP, it shows me it's calculating 6 days.

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

Please help. Thanks!

iAm_ManCat

Len vs IsBlank? Experience 😞

I have been caught too many times where IsBlank is considered 'technically false' because of a partial or previous value still being looked at, whereas Len always calculates the current Length of the item at the current moment in time, which should be zero if it's truly blank.

For varP, I can't say for certain but I think the defaultDate is still looking at the previous value of varP, so we could try using the direct datediff comparison instead of the variable?

With({
        PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
        WholeWeek: 7
    },
    With({
            DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
        },
        With({
                PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
            },
            With({
                    PrevThirdMonday: DateAdd(PrevFirstMonday, WholeWeek * 2),
                    PrevFourthMonday: DateAdd(PrevFirstMonday, WholeWeek * 3)
                },
                If( Len(MeetingB.SelectedDate)=0,
                    "", 
                    DateDiff( MeetingA.SelectedDate, MeetingB.SelectedDate,Days) <= 6, 
                    PrevThirdMonday,
                    PrevFourthMonday
                )
            )
        )
    )
)

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

View solution in original post

ljkeefe

@iAm_ManCat I figured it out by removing the variable varP and replacing it with a DateDiff statement using the PrevFourthMonday WITH variable. Here was the final expression:

With(
    {
        PrevFirstOfMonth: Date(
            Year(MeetingB.SelectedDate),
            Month(
                DateAdd(
                    MeetingB.SelectedDate,
                    -1,
                    Months
                )
            ),
            1
        ),
        WholeWeek: 7
    },
    With(
        {
            DayOfWeekPrevFirstOfMonth: Weekday(
                PrevFirstOfMonth,
                StartOfWeek.Tuesday
            )
        },
        With(
            {
                PrevFirstMonday: DateAdd(
                    PrevFirstOfMonth,
                    (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth)
                )
            },
            With(
                {
                    PrevThirdMonday: DateAdd(
                        PrevFirstMonday,
                        WholeWeek * 2
                    ),
                    PrevFourthMonday: DateAdd(
                        PrevFirstMonday,
                        WholeWeek * 3
                    )
                },
                If(
                    Len(MeetingB.SelectedDate) = 0,
                    "",
                    DateDiff(PrevFourthMonday,MeetingB.SelectedDate) <= 6,
                    PrevThirdMonday,
                    PrevFourthMonday
                )
            )
        )
    )
)

Thank you so much for your help!!

View solution in original post

iAm_ManCat · ‎05-30-2022

Hi @ljkeefe,

Are you setting varP on the Onchange of both datepickers?

What creates the PrevThirdMon and can we see the code for that? There's a lot of moving parts here so you'll need to share more for us to help (judging by the crazy number of closing brackets in your second snippet)

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

ljkeefe

Hi @iAm_ManCat ,

Thank you for the response and apologies for the delay, I was on vacation! I never know how much or how little information to provide but here it goes.

The varP variable is set on the OnChange property of the "Approved by" date picker under Meeting B. That Meeting B "Approved by" date picker has the following under Default Date:

If(IsBlank(MeetingB.SelectedDate),"",DateAdd(MeetingB.SelectedDate,-6,Days))

The OnChange property of Meeting B "Approved by" date picker is:

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

Meeting A date picker has the following on DefaultDate property, forgive all the WITH statements but it's the only way I could figure out how to do this:

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))))))))))))

The OTHER issue aside from my varP variable not calculating correctly is that the days of the week calculated by the WITH statement are off. As you can see in the various WITH statements above DayOftheWeek is set to 2 for Monday, the N is the week of the month. Since I'm counting backward, I thought to reverse the number for the days so Monday instead of 2 become 6, but that didn't work either.

Your help is appreciated.

iAm_ManCat

Ok, well let's break this down, we can refactor your code a bit. Sorry for the long explanation (please view this in the browser and not in your email), I will try to help you understand how to do less With and how to refactor your code into simpler terms. You can skip to the end for the answer if you want but I do recommend trying to understand it a bit more

Firstly, we neaten it up so we can see where everything is:

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)
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Next, I see that your day of week: 2 is the same everywhere, so we can re-use the original dayofweek1:

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),
            N2: 2
            }, 
            With({
                    SecondMonday: DateAdd(SecondOfMonthP, (N2 * 7) - Weekday(DateAdd(SecondOfMonthP, 7 - DayOfWeek1)))
                },
                With({
                    ThirdOfMonthP: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                    N3: 3
                    }, 
                    With({
                            ThirdMonday3: DateAdd(ThirdOfMonthP, (N3 * 7) - Weekday(DateAdd(ThirdOfMonthP, 7 - DayOfWeek1)))
                        },
                        With({
                            FourthOfMonthP: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                            N4: 4
                            }, 
                            With({
                                    FourthMondayP: DateAdd(FourthOfMonthP, (N4 * 7) - Weekday(DateAdd(FourthOfMonthP, 7 - DayOfWeek1)))
                                },
                                With({
                                        PrevFourth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                                        N: 1
                                    },
                                    With({
                                            PrevFourthMon: DateAdd(DateAdd(PrevFourth, -1, Months), (N4 * 7) - Weekday(DateAdd(PrevFourth, 7 - DayOfWeek1)))
                                        },
                                        With({
                                                PrevThirdMon: DateAdd(DateAdd(PrevFourth, -1, Months), (N3 * 7) - Weekday(DateAdd(PrevFourth, 7 - DayOfWeek1)))
                                            },
                                            If(IsBlank(MeetingB), "", varP <= 6, PrevThirdMon, PrevFourthMon)
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Then we can take out the N1 and etc and just use 1,2,3 etc:

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    DayOfWeek1: 2
    }, 
    With({
        FirstMonday: DateAdd(FirstMonth, (1 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
        },
        With({
            SecondOfMonthP: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
            }, 
            With({
                    SecondMonday: DateAdd(SecondOfMonthP, (2 * 7) - Weekday(DateAdd(SecondOfMonthP, 7 - DayOfWeek1)))
                },
                With({
                    ThirdOfMonthP: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                    }, 
                    With({
                            ThirdMonday3: DateAdd(ThirdOfMonthP, (3 * 7) - Weekday(DateAdd(ThirdOfMonthP, 7 - DayOfWeek1)))
                        },
                        With({
                            FourthOfMonthP: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                            }, 
                            With({
                                    FourthMondayP: DateAdd(FourthOfMonthP, (4 * 7) - Weekday(DateAdd(FourthOfMonthP, 7 - DayOfWeek1)))
                                },
                                With({
                                        PrevFourth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
                                    },
                                    With({
                                            PrevFourthMon: DateAdd(DateAdd(PrevFourth, -1, Months), (4 * 7) - Weekday(DateAdd(PrevFourth, 7 - DayOfWeek1)))
                                        },
                                        With({
                                                PrevThirdMon: DateAdd(DateAdd(PrevFourth, -1, Months), (3 * 7) - Weekday(DateAdd(PrevFourth, 7 - DayOfWeek1)))
                                            },
                                            If(IsBlank(MeetingB), "", varP <= 6, PrevThirdMon, PrevFourthMon)
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Then I can see that your FirstMonth, SecondOfMonthP, ThirdOfMonthP, FourthOfMonthP and PrevFourth all use the exact same formula, so we can just refer to FirstMonth in each of those cases:

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    DayOfWeek1: 2
    }, 
    With({
        FirstMonday: DateAdd(FirstMonth, (1 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
        },
        With({
            }, 
            With({
                    SecondMonday: DateAdd(FirstMonth, (2 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
                },
                With({
                    }, 
                    With({
                            ThirdMonday3: DateAdd(FirstMonth, (3 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
                        },
                        With({
                            }, 
                            With({
                                    FourthMondayP: DateAdd(FirstMonth, (4 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
                                },
                                With({
                                    },
                                    With({
                                            PrevFourthMon: DateAdd(DateAdd(FirstMonth, -1, Months), (4 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
                                        },
                                        With({
                                                PrevThirdMon: DateAdd(DateAdd(FirstMonth, -1, Months), (3 * 7) - Weekday(DateAdd(FirstMonth, 7 - DayOfWeek1)))
                                            },
                                            If(IsBlank(MeetingB), "", varP <= 6, PrevThirdMon, PrevFourthMon)
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Now we don't need all of those With statements as only the last section relies on any of the other values:

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    DayOfWeek1: 2
    }, 
    With({
        FirstMonday:   DateAdd( FirstMonth, (1 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        SecondMonday:  DateAdd( FirstMonth, (2 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        ThirdMonday3:  DateAdd( FirstMonth, (3 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        FourthMondayP: DateAdd( FirstMonth, (4 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        PrevFourthMon: DateAdd( DateAdd( FirstMonth, -1, Months), (4 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        PrevThirdMon:  DateAdd( DateAdd( FirstMonth, -1, Months), (3 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1)))
        },
        If(IsBlank(MeetingB), "", varP <= 6, PrevThirdMon, PrevFourthMon)
    )
)

Now we should also fix that we are referencing a control directly, so IsBlank MeetingB becomes Len(MeetingB.SelectedDate)=0

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    DayOfWeek1: 2
    }, 
    With({
        FirstMonday:   DateAdd( FirstMonth, (1 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        SecondMonday:  DateAdd( FirstMonth, (2 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        ThirdMonday3:  DateAdd( FirstMonth, (3 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        FourthMondayP: DateAdd( FirstMonth, (4 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        PrevFourthMon: DateAdd( DateAdd( FirstMonth, -1, Months), (4 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))),
        PrevThirdMon:  DateAdd( DateAdd( FirstMonth, -1, Months), (3 * 7) - Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1)))
        },
        If(Len(MeetingB.SelectedDate)=0, "", varP <= 6, PrevThirdMon, PrevFourthMon)
    )
)

Then I can see you are using the same Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1)) code in a few places so we can make that a variable called WeekdayMinus:

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    DayOfWeek1: 2
    },
    With({
         WeekdayMinus: Weekday( DateAdd( FirstMonth, 7 - DayOfWeek1))
        },
        With({
            FirstMonday:   DateAdd( FirstMonth, (1 * 7) - WeekdayMinus),
            SecondMonday:  DateAdd( FirstMonth, (2 * 7) - WeekdayMinus),
            ThirdMonday3:  DateAdd( FirstMonth, (3 * 7) - WeekdayMinus),
            FourthMondayP: DateAdd( FirstMonth, (4 * 7) - WeekdayMinus),
            PrevFourthMon: DateAdd( DateAdd( FirstMonth, -1, Months), (4 * 7) - WeekdayMinus),
            PrevThirdMon:  DateAdd( DateAdd( FirstMonth, -1, Months), (3 * 7) - WeekdayMinus)
            },
            If(Len(MeetingB.SelectedDate)=0, "", varP <= 6, PrevThirdMon, PrevFourthMon)
        )
    )
)

Then I can see you referring to the previous first of month at the end so lets make that a variable:

With({
    FirstMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
    PrevFirstMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd( MeetingB.SelectedDate, -1, Months)), 1),
    DayOfWeek: Weekday(MeetingB.SelectedDate),
    },
    With({
         WeekdayMinus: Weekday( DateAdd( FirstMonth, 7 - DayOfWeek))
        },
        With({
            FirstMonday:   DateAdd(     FirstMonth, (1 * 7) - WeekdayMinus),
            SecondMonday:  DateAdd(     FirstMonth, (2 * 7) - WeekdayMinus),
            ThirdMonday3:  DateAdd(     FirstMonth, (3 * 7) - WeekdayMinus),
            FourthMondayP: DateAdd(     FirstMonth, (4 * 7) - WeekdayMinus),

            PrevFourthMon: DateAdd( PrevFirstMonth, (4 * 7) - WeekdayMinus),
            PrevThirdMon:  DateAdd( PrevFirstMonth, (3 * 7) - WeekdayMinus)
            },
            If(Len(MeetingB.SelectedDate)=0, "", varP <= 6, PrevThirdMon, PrevFourthMon)
        )
    )
)

Now we can expand that a bit to get a formula we can put in a label to see all of the debug values after I've tweaked it a bit:

With({
        FirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(MeetingB.SelectedDate), 1),
        PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
        WholeWeek: 7
    },
    With({
            DayOfWeekFirstOfMonth: Weekday(FirstOfMonth, StartOfWeek.Tuesday),
            DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
        },

        With({
                FirstMonday: DateAdd(FirstOfMonth, (1 * WholeWeek) - (DayOfWeekFirstOfMonth)),
                PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
            },
            With({

                    SecondMonday: DateAdd(FirstMonday, WholeWeek * 1),
                    ThirdMonday: DateAdd(FirstMonday, WholeWeek * 2),
                    FourthMonday: DateAdd(FirstMonday, WholeWeek * 3),
                    PrevSecondMonday: DateAdd(PrevFirstMonday, WholeWeek * 1),
                    PrevThirdMonday: DateAdd(PrevFirstMonday, WholeWeek * 2),
                    PrevFourthMonday: DateAdd(PrevFirstMonday, WholeWeek * 3)
                },

                If(true,
                    //Len(MeetingB.SelectedDate)=0, "", varP <= 6, 
                    //PrevThirdMon, PrevFourthMon

                    "FirstOfMonth " & FirstOfMonth & Char(10) &
                    "DayOfWeekFirstOfMonth " & DayOfWeekFirstOfMonth & Char(10) &
                    "FirstMonday " & FirstMonday & Char(10) &
                    "SecondMonday " & SecondMonday & Char(10) &
                    "ThirdMonday " & ThirdMonday & Char(10) &
                    "FourthMonday " & FourthMonday & Char(10) &
                    "PrevFirstOfMonth " & PrevFirstOfMonth & Char(10) &
                    "DayOfWeekPrevFirstOfMonth " & DayOfWeekPrevFirstOfMonth & Char(10) &
                    "PrevFirstMonday " & PrevFirstMonday & Char(10) &
                    "PrevSecondMonday " & PrevSecondMonday & Char(10) &
                    "PrevThirdMonday " & PrevThirdMonday & Char(10) &
                    "PrevFourthMonday " & PrevFourthMonday & Char(10)

                )

            )
        )
    )
)

The above should help you understand how the date math works a little better,

Then finally, you can use this as your defaultdate (since you dont use the original first second etc dates):

With({
        PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
        WholeWeek: 7
    },
    With({
            DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
        },
        With({
                PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
            },
            With({
                    PrevThirdMonday: DateAdd(PrevFirstMonday, WholeWeek * 2),
                    PrevFourthMonday: DateAdd(PrevFirstMonday, WholeWeek * 3)
                },
                If( Len(MeetingB.SelectedDate)=0, "", 
                    varP <= 6, 
                    PrevThirdMonday,
                    PrevFourthMonday
                )
            )
        )
    )
)

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

ljkeefe

@iAm_ManCat Oh my goodness, thank you! That is so much more digestible. I really appreciate you breaking it down for me.

Two problems/questions:

1. I don't understand why you use LEN vs. ISBLANK

2. My varP is still not calculating correctly.

When I put the variable (varP) in a label, it calculates the Date diff between Meeting B "Approved by" date and the Meeting A "Meeting Date" correctly. As soon here, it is 2 days. However, the varP in the Meeting Date date picker is showing 9 as you can see in the upper left-hand corner of the image. This is causing the IF statement to not work correctly. In this case, the Meeting Date for Meeting A should be the third Monday of July not the fourth.

iAm_ManCat

Len vs IsBlank? Experience 😞

I have been caught too many times where IsBlank is considered 'technically false' because of a partial or previous value still being looked at, whereas Len always calculates the current Length of the item at the current moment in time, which should be zero if it's truly blank.

For varP, I can't say for certain but I think the defaultDate is still looking at the previous value of varP, so we could try using the direct datediff comparison instead of the variable?

With({
        PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
        WholeWeek: 7
    },
    With({
            DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
        },
        With({
                PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
            },
            With({
                    PrevThirdMonday: DateAdd(PrevFirstMonday, WholeWeek * 2),
                    PrevFourthMonday: DateAdd(PrevFirstMonday, WholeWeek * 3)
                },
                If( Len(MeetingB.SelectedDate)=0,
                    "", 
                    DateDiff( MeetingA.SelectedDate, MeetingB.SelectedDate,Days) <= 6, 
                    PrevThirdMonday,
                    PrevFourthMonday
                )
            )
        )
    )
)

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

ljkeefe

@iAm_ManCat Ah, yes, experience. Got it.

When I used DateDiff directly, I got a circular reference error. That's why I created the varP variable. If you know of a workaround for the circular reference error, then I might be in business.

iAm_ManCat

Ok, so I think we might be able to get around this if we check for varP on the outer side of the formula and then do the calcs inside:

If( Len(MeetingB.SelectedDate)=0,
    "", 
    varP <= 6, 
    With({
            PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
            WholeWeek: 7
        },
        With({
                DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
            },
            With({
                    PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
                },
                With({
                        PrevThirdMonday: DateAdd(PrevFirstMonday, WholeWeek * 2)
                    },
                        PrevThirdMonday
                    )
                )
            )
        )
        ,
        //Otherwise use the PrevFourthMonday
        With({
            PrevFirstOfMonth: Date(Year(MeetingB.SelectedDate), Month(DateAdd(MeetingB.SelectedDate, -1, Months)), 1),
            WholeWeek: 7
        },
        With({
                DayOfWeekPrevFirstOfMonth: Weekday(PrevFirstOfMonth, StartOfWeek.Tuesday)
            },
            With({
                    PrevFirstMonday: DateAdd(PrevFirstOfMonth, (1 * WholeWeek) - (DayOfWeekPrevFirstOfMonth))
                },
                With({
                        PrevFourthMonday: DateAdd(PrevFirstMonday, WholeWeek * 3)
                    },
                        PrevFourthMonday
                    )
                )
            )
        )
)

Could you give that a try and let me know if that's any better?

Oh and the other explanation about the IsBlank you had, you had it pointed directly at the control name

IsBlank(MeetingB)

..And you would assume that it knows to look at the selectedDate property - but rarely that can fail where it can't determine which property it should be looking at as a default so you should also always explicitly define which property you are checking for IsBlank or looking at Len - the datepicker is the one I always use Len on as once the default date changes to an invalid state it can technically still have a date so the IsBlank says its not blank even though it has an error value, whereas Len of an error is also zero

IsBlank(MeetingB.SelectedDate)

IsBlank(MeetingB.X)

IsBlank(MeetingB.Height)

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

ljkeefe

@iAm_ManCat

I tried the above and varP is still calculating 9 when there is only 2 day difference between the two date pickers. I'm assuming this is why it's not applying the condition of the IF statement.

I have varP Set in the OnChange of the Meeting B date picker (which isn't one of the two date pickers involved but they are dependent on the date in this date picker). Not sure if that makes a difference.

I want to say thank you for hanging in here with me and helping.

iAm_ManCat

No worries! glad to help, one day hopefully you'll do the same for someone else 🙂

Ok, so we need to look at how varP is being generated.

I think this should be set on both OnChange of MeetingA and MeetingB

Because right now only a change to MeetingB will change varP right? so once its set then any change to MeetingA won't affect it, even though varP isreliant on MeetingA.

How is the DefaultDate for MeetingB derived?

@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

DATEDIFF variable not calculating correctly

Helpful resources

Join us for Microsoft Power Platform Conference

Register for a Free Workshop

New Release Planning Portal (Preview)