Mar 16, 2023 02:54 AM
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!
Solved! Go to Solution.
Mar 17, 2023 03:56 PM
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")))))
Mar 16, 2023 08:35 AM
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!
Mar 16, 2023 01:34 PM
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?
Mar 16, 2023 08:09 PM
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"), ...
Mar 17, 2023 05:55 AM
@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?
Mar 17, 2023 07:57 AM
@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!
Mar 17, 2023 10:02 AM
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?
Mar 17, 2023 03:56 PM
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")))))
Mar 23, 2023 03:06 AM
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?
Mar 23, 2023 10:02 AM
@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....
)