Find the next Wednesday based on an already given date

plyske · ‎Mar 16, 2023

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!

plyske · ‎Mar 28, 2023

@AirOps You are absolutely fantastic. Thanks!

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

IF(AND({Trækdato (FarPay)},{Dato for udløb}), 
    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"))=2, 
            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"))<2, 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),2-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),9-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days")))))

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?

plyske · ‎Mar 28, 2023

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

IF(AND({Trækdato (FarPay)}, {Dato for udløb}, MONTH({Trækdato (FarPay)})=MONTH({Dato for udløb})), 
    IF(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"))=2, 
            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"))<2, 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),2-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD"),9-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Dato for udløb}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"))))