Unanswered

Set a field to the first Wednesday of the next month

(0) Share

Report

Posted on by dloseff

Hi All,

I have a need to set a field to the first Wednesday of the month after a date specified in a date picker. The field will not be visible, and it could be a Date/Time type, or a Single Line of Text type. It will be stored in a Sharepoint list, so that field can change to match the Power App approach. Or the calculation could be done in Sharepoint.

I found the following (which works for the first Friday of the next month) on another forum,

=IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1;DATE(YEAR(Data1);MONTH(Data1)+1;1+1);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=7;DATE(YEAR(Data1);MONTH(Data1)+1;1+2);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=6;DATE(YEAR(Data1);MONTH(Data1)+1;1+3);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=5;DATE(YEAR(Data1);MONTH(Data1)+1;1+4);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=4;DATE(YEAR(Data1);MONTH(Data1)+1;1+5);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=3;DATE(YEAR(Data1);MONTH(Data1)+1;1+6);DATE(YEAR(Data1);MONTH(Data1)+1;1)))))))

an adapted it in various ways, but I could not get it to work. Among my issues:

- attempting to create a calculated field in Sharepoint: formula too long - it was truncated,

- attempted to place in the default value of a Date/Time field in Power Apps

- attempted to place in a "Single Line of Text" field in Power Apps

- attempted to place as text in a label in Power Apps.

The Power App errors were too long to correct!

Nothing worked. Any help would be greatly appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

timl 36,383 Super User 2025 Season 2 on at

Like (3)

Report
Hi @dloseff

This formula will return the 1st Wednesday of the next month. You would need to replace DatePicker1 with the name of your date picker.

With( { FirstOfNextMonth: DateAdd( Date( Year(DatePicker1.SelectedDate), Month(DatePicker1.SelectedDate), 1 ), 1, Months ) }, DateAdd( FirstOfNextMonth, 7 - Weekday( DateAdd( FirstOfNextMonth, 3 ) ) ) )

Was this reply helpful? Yes No
dloseff 9 on at

Like (0)

Report

Wonderful! Thanks so much for your quick, simple and accurate response! Worked like a charm

Was this reply helpful? Yes No
dloseff 9 on at

Like (0)

Report

So here is a new twist to this request. Originally I needed to find the first Wed of the month after a selected date. The selected date is a new employee's start date, the first Wednesday of the next month is the date of new employee orientation. But the twist is this: if the employee starts on a date in the same month as, but prior to the first Wed, then their orientation would be on that month's first Wed. As an example, if an employee starts on Friday, Oct. 1, they should be scheduled for the orientation taking place on Wed, Oct 6, not Wed, Nov 1. Any ideas on how to approach this? Thanks!

Was this reply helpful? Yes No