Answered

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

All responses (3)

Answers (1)

Sort by

Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(3)

Report
Copy link

Link copied!
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 ) ) ) )
dloseff 9 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

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!