Help

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

Solved
Jump to Solution
1391 1
cancel
Showing results for 
Search instead for 
Did you mean: 
plyske
7 - App Architect
7 - App Architect

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!

1 Solution

Accepted Solutions
AirOps
7 - App Architect
7 - App Architect

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

 

 

 

See Solution in Thread

11 Replies 11
AirOps
7 - App Architect
7 - App Architect

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! 

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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

@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
7 - App Architect
7 - App Architect

@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
7 - App Architect
7 - App Architect

@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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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
7 - App Architect
7 - App Architect

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

)

 

plyske
7 - App Architect
7 - App Architect

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

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