Excel - Find Date/Days to use the values

[arvarr]

Hi
I need help to take current year's value and populate in next year (but there is a dependency on the "day").

A1 - 01-01-20 - Wed = 1 (Wed is the Distribution Day)
Plus 365 days later = 31-12-20 which is a Thursday. Will have to push to next Wed's date.

Will require to do same for whole year.

Thanks,
Arv

 

[SkipVought]

Hi,

[pre]
1/1/20 1/2/20 1/3/20 1/4/20 1/5/20 1/6/20 1/7/20 1/8/20
Wed Thu Fri Sat Sun Mon Tue Wed
1 2 3 4 5 6 7 8

[highlight #FCE94F]1/6/21[/highlight] . [highlight #FCAF3E]1/7/21[/highlight] 1/8/21 1/9/21 1/10/21 1/11/21 1/12/21 1/13/21
[highlight #8AE234]Wed[/highlight] Thu Fri Sat Sun Mon Tue Wed
[/pre]

[tt]
A5:[highlight #FCE94F]=LET(a,A1+365,7-WEEKDAY(a)+4+a)[/highlight]
B5:[highlight #FCAF3E]=A5+1[/highlight]
A6:[highlight #8AE234]=TEXT(A5,"ddd")[/highlight]
[/tt]
...COPY B5 and PASTE across.

Explanation: WEEKDAY(a) where a=1/1/2020+365 returns the day of the week (1-7) for the given date value, a.
So to advance to the NEXT Wed, day 4,
1) calculate the remaining days in the week [7-WEEKDAY(a)] and
2) add 4 to get to Wed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Hi Skip
Apologies for the late response.

Thanks for the formula.

Let me give that a try and will revert back.

Many Thanks as usual. You're a Champ!

Regards,
Arv

 

[SkipVought]

I've slept since then.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein