In excel power query, I need to calculate the date of a given weekday in the week following a given date. For example, given 3/17/2022 and Wednesday, I need to find the date that the Wednesday following 3/17/2022 will fall on (3/23/2022). Need to do this as a custom column formula.

My approach is to add one to the week number, but I cannot figure out how to produce a date given the year, week number, and a DayOfWeek.

Some context: I am calculating estimated delivery dates using order dates and turn-around timeframes. Turn-arounds are in the format of "Thurs noon following Wed" meaning the deadline to place the order is Thursday at noon and the soonest it can be delivered will be the Wednesday of the following week.

CodePudding user response：

There are many ways to do this given a start date and a day number for the following week. Here is one:

Date.AddDays(Date.StartOfWeek(Date.AddDays([StartDate],7), Day.Monday), [DayNum] - 1)

I'll assume the field names are self-explanatory. :)

It basically adds 7 to the date, gets the Monday of that week, and then adds the required day number less 1 (eg if Weds is day 3, you only want to add 2 days to Monday)