This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Help

Re: Find the next Wednesday based on an already given date

Solved
Jump to Solution
1425 1
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
plyske
7 - App Architect
7 - App Architect
See Solution in Thread

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

0 Kudos
Reply
11 Replies 11
plyske
7 - App Architect
7 - App Architect
See Solution in Thread

‎Mar 28, 2023 03:58 AM

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

0 Kudos
Reply
plyske
7 - App Architect
7 - App Architect
In response to plyske
See Solution in Thread

‎Mar 28, 2023 04:11 AM

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"))))
0 Kudos
Reply
Copyright © 2024 Khoros, LLC