Find day of the week next year

[info overload]

Hello all,

I am building a sheet where I am comparing sales to the same day of the week between years and account for leap year. So in the same row I need the Mondays to line up. "=Cell+365" doesn't work for either of my requirements.

In my research I found this formula: =DATE(YEAR(B7)-1,MONTH(B7),DAY(B7))+WEEKDAY(B7)-WEEKDAY(DATE(YEAR(B7)-1,MONTH(B7),DAY(B7))) it figures the previous year and rearranging the formula it is so close but it screws up the leap year as you can see in my example.

Any advise from the excel wizards?

Thanks in advance.

 

[Ron Coderre]

With a date in B7..
If you have a version of Excel with the WORKDAY.INTL function....try this regular formula:

=WORKDAY.INTL(B7,52,RIGHT(11111111-10^(8-WEEKDAY(B7,2)-1),7))

(The 3rd argument would typically be a 7-character text string containing some combination of 1's and 0's
where 1's represent non-workdays and 0's represent workdays...Starting with Mondays. So 1101111 would only count Wednesdays as workdays.
That formula builds the text string based on the weekday of the referenced cell.)

Is that something you can work with?

 

[JLGWhiz]

Another way with date in B7

=WEEKDAY(DATE(YEAR(B7) +1,MONTH(B7), DAY(B7)))

 

[info overload]

Ron: I don't think I have a version that can use this file. I am on old version of excel.

JLG: When I tried your formula I get a single number as apposed to a date When I convert to a date format I am getting the year 1900.

 

[JLGWhiz]

The single number should be the day of the week with 1 being Sunday and 7 being Saturday. Did you want the day Spelled out?

=TEXT(WEEKDAY(DATE(YEAR(B7) +1,MONTH(B7), DAY(B7))), "dddd")

 

[Ron Coderre]

(edited to streamline the formula a bit)
I just checked my formula stash and re-discovered this little gem.

=B7+365-WEEKDAY(B7-WEEKDAY(B7,2))

Is that something you can use?

 

[info overload]

Ron: This is what I need thank you!

JLG: Thank you for your efforts.

 

[Tetra201]

... I just checked my formula stash and re-discovered this little gem.

=B7+365-WEEKDAY(B7-WEEKDAY(B7,2))

Isn't it the same as =B7+364 ?

 

[Ron Coderre]

Isn't it the same as =B7+364 ?

Not in a Leap Year...That's the issue mentioned in post #1
edited to include this:
Actually, that's not true...adding 364 returns the same day....So....I'm not sure what the issue is :/

 

[Tetra201]

Not in a Leap Year...

Did you have a chance to check that?

This part of your formula

WEEKDAY(B7-WEEKDAY(B7,2))

seems to always return 1.