Formula to show every tuesday and friday

Howdy

Bear with me

* Every bill is expected to be paid on a due date, which is the next tuesday after it was received

Lets take this as an example

Bill received__________Expected payment date
31/01/2019__________=formula????

What is the formula to display the next tuesday after the date "bill received"?

What's your expected result for each line? I'm not sure I understand your requirement fully.

Originally Posted by CK76

What's your expected result for each line? I'm not sure I understand your requirement fully.

Date received_________Expected Payment date__________________________Days late
31/01/2019__________=formula to bring out next tuesday(05/02/2019)_____=today-expected payment date
25/12/2018__________=formula to bring out next tuesday(01/01/2019)_____=today-expected payment date


The field expected payment date should bring the tuesday after the field date received

not elegant but
=A1+7-IF(WEEKDAY(A1)=3,0,WEEKDAY(A1-3))

then if the above is in b1
=today()-b1

So, then my 2nd formula works for your sample.

What else is needed?

Originally Posted by davsth

not elegant but
=A1+7-IF(WEEKDAY(A1)=3,0,WEEKDAY(A1-3))

then if the above is in b1
=today()-b1

This kind worked, but help me out to understand what is happening here
I'm not very logic driven nor excel savy, so this isn't very clear
=A1+7-IF(WEEKDAY(A1)=3,0,WEEKDAY(A1-3))
A few questions
-> What does the "=3" represents?
-> What does the "-3" represents?
-> If they represent tuesday - is it a hard reference to the week day (example: Excel understand that tuesday=3, wednesday=4...) or the decision to this particular number takes in account the present day?

Originally Posted by CK76

So, then my 2nd formula works for your sample.

What else is needed?

Does your formula takes in account the received date as tuesday, because what i need is a formula that doesn't take in account when the bill was received. In other words, regardless of the received date, the payment date should be the exact next tuesday of the received date.

And if the bill doesn't get paid the next proposed tuesday, it should then start counting late dates with the =X-Y formula

On using =A2+7-WEEKDAY(A2-3)*(WEEKDAY(A2)<>3)
What if i were to change the payment date to friday?

In WEEKDAY() function, without optional argument for return type, Sunday is considered first day of week.
Sun to Sat is assigned numbers 1 to 7.

There is 7 days in a week... so by adding 7 to current day, you are in next week on same day '2/7/2019'.
Subtract Tuesday's value (3) from current date's weekday value (ex. Today '1/31/2019' = 5).
This will give you number of days to go back to Tuesday.
So '2/7/2019' - 2 = '2/5/2019'.
If function is used to check if the date in A1 falls on Tuesday. If it does, just add 7 to current date to get next Tuesday.

Same logic is used for =A2+7-WEEKDAY(A2-3)*(WEEKDAY(A2)<>3)
As false in Excel evaluate to 0. If A2 is Tuesday...
=A2+7-(2)*(0)
=A2+7

Otherwise...
=A2+7-(2)*(1)

Originally Posted by CK76

In WEEKDAY() function, without optional argument for return type, Sunday is considered first day of week.
Sun to Sat is assigned numbers 1 to 7.

There is 7 days in a week... so by adding 7 to current day, you are in next week on same day '2/7/2019'.
Subtract Tuesday's value (3) from current date's weekday value (ex. Today '1/31/2019' = 5).
This will give you number of days to go back to Tuesday.
So '2/7/2019' - 2 = '2/5/2019'.
If function is used to check if the date in A1 falls on Tuesday. If it does, just add 7 to current date to get next Tuesday.

Same logic is used for =A2+7-WEEKDAY(A2-3)*(WEEKDAY(A2)<>3)
As false in Excel evaluate to 0. If A2 is Tuesday...
=A2+7-(2)*(0)
=A2+7

Otherwise...
=A2+7-(2)*(1)

Awesome
I now understand the 2nd formula you proposed
Worked as expected.

Another question, how can i sum months?
like
01/01/2019 + =formula????? = 01/01/2019
01/02/2019 + =formula???? = 01/02/2019

Hmm? Not sure I get you.

But if you mean that you want to add x number of months to given date...
=EOMONTH(Date,#ofMonth-1)+Day(Date)

Ex: Where A2 holds Jan 2nd 2019 and add 1 month.
=EOMONTH(A2,1-1)+Day(A2)
=1/31/2019+2 = 2/2/2019

Alternately, where +1 is # of month to add.
=Date(Year(A2),Month(A2)+1,Day(A2))