Find the next Wednesday based on an already given date

[plyske]

Hey everyone! 

I need help making a formula that gives me the date of the next coming Wednesday based on another date in a date field. 

So for example if the date field shows 3/4/23, I want the formula to return 5/4/23. 

Thanks in advance!

[AirOps]

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"))))

I hope this helps! 

[plyske]

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?

[AirOps]

Hi @plyske 

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

Try this: 

IF(DATETIME_FORMAT(date, "D")<10,

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"),

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD")),"days"),

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD")),"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"))))
)

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:

IF(WEEKDAY(date)=3, date), ...

with this: 

IF(WEEKDAY(date)=3, DATEADD(date, 1 , "weeks"), ...

and this 

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"),...

with this

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT(date, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), ...

[plyske]

@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? 

[AirOps]

@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!

[plyske]

@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?

[AirOps · Accepted solution]

Makes sense! Thank you for clarifying. Here is a formula that should work: 

IF(AND(DATETIME_FORMAT({Date_field_1}, "D")<10, DATETIME_FORMAT({Date_field_2}, "D")<10),

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), 

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), 

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Date_field_2}, "YYYY-MM-10"),"YYYY-MM-DD")),"days")))))

[plyske]

Hi @AirOps 

Can you help me with some validation? 

Right now this formula: 

IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Dato for udløb}, "D")<10),

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))=3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), 

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))<3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),3-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), 

IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"))>3, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),10-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days")))))

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

[AirOps]

@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....

)