Find the next Wednesday based on an already given date

Hi @plyske! 

Try this formula: 

IF(WEEKDAY(date)=3, date,

IF(WEEKDAY(date)<3, DATEADD(date,3-WEEKDAY(date),"days"),

IF(WEEKDAY(date)>3, DATEADD(date,10-WEEKDAY(date),"days"))))

IF(WEEKDAY(date)=3, date,

IF(WEEKDAY(date)<3, DATEADD(date,3-WEEKDAY(date),"days"),

IF(WEEKDAY(date)>3, DATEADD(date,10-WEEKDAY(date),"days"))))

Hi, @AirOps 

Thanks a lot! That seems to be what I was looking for! 

Can you help me expand the formula a bit? 

IF the date of the (Date) field is BEFORE the 10th of the month, I want to have shown (in my formula field) the first wednesday AFTER the 10th of the month. 

Is that possible?

Hi @plyske 

It definitely is possible, but the formula is going to be quite long! 

Try this: 

ps! I realized in my first formula that you if your input date is a Wednesday you might want the result to be the following Wednesday, if this is the case replace this:

with this: 

and this 

with this

@AirOps That's simply fantastic! 
I have one last issue (I hope!)

I have 2 date fields (Date_field_1) and (Date_field_2)

Let's say that Date_field_1 show 1/5/23 and Date_field_2 show 7/5/23, then I want to, in the formula field, have shown the first wednesday after the 10th. 

In other words: IF the two dates shown in the date fields BOTH are among the 10 first dates of the month, in the same month, then I want to have shown the first wednesday of that month AFTER the 10th. 

Does that make sense? 

@plyske, could you please let me know what you would like for the formula todo if one of the two input dates are past the 10th of the month? 

Then I should be able to sort this out!

@AirOps 

So the first date field (Date_field_1) will never be past the 10th since that's how the payment method works. 

The second date field could be past the 10th, but in that case I use one of your previous formulas to fix that. 

So for this scenario the formula should only work if BOTH dates are withtin the first 10 days of the month. If the second date field is past the first 10 days, then just leave the field blank. 

Makes sense?

Hi @AirOps 

Can you help me with some validation? 

Right now this formula: 

returns an #ERROR! when the criteria isn't met. Can you help me fix this so that it just leaves the field blank instead?

@plyske Of course! 

The reason why you are seeing an error is because if the date field is not complete, unable to be executed. To fix this you can add a simple IF statement around your formula to check if the date fields have been filled in, and only execute when that is satisfied. 

It would look like this: 

IF(AND({Trækdato (FarPay)},{Dato for udløb}), 

.......the rest of the formula here....

)

@AirOps You are absolutely fantastic. Thanks! 

In this formula (one of those you helped me with): 

it currently looks at two date fields 'Trækdato (FarPay)' and 'Dato for udløb'. However there is an error here. This formula should only return a value if the date in both these date fields are within the same month. 

Right now it looks at 'Trækdato (FarPay)' and if that date field has a value within the first 10 days of the month, it will give me a date, but if the date in 'Trækdato (FarPay) is after the first 10 days of the month, it won't. This is just to tell how the formula field works at the moment.. As mentioned the formula field should only return a value if 'Trækdato (FarPay)' and 'Dato for udløb' dates are within the same month. 

Can you help me? 

I've tried this, but it won't accept it: