Date Picker Help

[DLCaterhamSch]

Dear All,

I need to reset a date from a date picker is:

Less than today

Greater than today at 9:10 am UK Time

Greater than 31 working days (not weekends)

Any help with this would be greatly appreciated. I have the following which meets some of the criteria but not all.

If(Self.SelectedDate < Today() || Self.SelectedDate > Today() + 31||Weekday(Self.SelectedDate,StartOfWeek.Monday)=6||Weekday(Self.SelectedDate,StartOfWeek.Monday)=7, Reset(Self))

Kind Regards

Daniel

[Chris-D]

Hi @DLCaterhamSch,

2 of your conditions seem to conflict? 

Greater than today at 9:10 am UK Time

Greater than 31 working days (not weekends)

How can the date be up to 31 days in advance, if it cannot be beyond 9.10am on the same day? 

[Prabhakar_S]

Hi @DLCaterhamSch ,

Please try this formula,

If(
Self.SelectedDate < Today(),
Reset(Self),
If(
Self.SelectedDate > DateAdd(Today(), Time(9, 10, 0)),
Reset(Self),
If(
DateDiff(Today(), Self.SelectedDate) > 31,
Reset(Self)
)
)
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[DLCaterhamSch]

Thank you for the help, but this part of the formula doesnt appear to work? 

If(
Self.SelectedDate > DateAdd(Today(), Time(9, 10, 0)),
Reset(Self),

[Chris-D]

Of course it doesn't. He also doesn't allow for 31 weekdays, he includes weekends. 

Could you please elaborate on my question above? 

[DLCaterhamSch]

So I need the following:

Clear date if:

less than today

if greater than 9:10 today but less than end of that day

From next day allow up until a month and not weekends then anything further clear

[Chris-D · Accepted solution]

ok I see. Try this. It's not tested, let me know if there are issues:

If(
    // Date before today, easy one
    Self.SelectedDate < Today()

    // Or date is after 9:10 today, we need to build a datetime object for this one
    // We can use Today() to get today's date, then specify the time
    || (
        Self.SelectedDate = Today()
        && Now() > DateTime( Year(Today()), Month(Today()), Day(Today()), 9, 10, 0)
    )

    // Working days is a bit more complicated....
    // Luckily I've solved this previously for another question

    // Use With() to set up some input parameters
    || With({
        StartDate: Today(),
        EndDate: Self.SelectedDate
    },
        With({
            // Make sure our dates are the right way around
            PastDate: Min( StartDate, EndDate),
            FutureDate: Max( StartDate, EndDate)
        },
            With({
                // Work out total days, add 1 to include the last day
                Days: DateDiff( PastDate, FutureDate) +1,
    
                // Work out how many working days in the first week and last week
                // This corrects for partial weeks in range
                WorkingDaysBefore: Max( 0, 8- Weekday( PastDate, StartOfWeek.Monday) -2),
                WorkingDaysAfter: Min( 5, Weekday( FutureDate, StartOfWeek.Monday))
            },
                With({
                    // Int() removes numbers after the decimal point
                    // This gives us the number of whole weeks, less the days we already counted
                    Weeks: Int((Days -WorkingDaysBefore -WorkingDaysAfter) /7)
                },
                    // The answer is the weekdays in whole weeks
                    // Add on the weekdays in the partial weeks before and after
                    With({
                        WorkingDays: (Weeks *5) +WorkingDaysBefore +WorkingDaysAfter
                    },
                        WorkingDays > 31
                    )
                )
            )
        )
    ),

    // Reset control if one of the above is true
    Reset( Self);
)

[DLCaterhamSch]

Thank you but this has many errors?

[Chris-D]

Sorry, couple of syntax errors. Fixed it now.

[DLCaterhamSch]

This appears to work some what, it doesn't clear after a month and allows weekends?