CODEWITHSUNDEEP
exceldate
L.Dutch
L.Dutch

Reputation: 966

Formula to calculate dates

In my Excel sheet I want to calculate the dates of begin/end of 4 weeks. I have this formula to calculate Monday and Sunday dates based on week number entered in C9, H9 (= C9+1) , C23 (=H9+1) , H23 (=C23+1) :

For Mondays

=IF(C9<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(C9,1,3))+C9*7,"")
=IF(H9<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(H9,1,3))+H9*7,"")
=IF(C23<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(C23,1,3))+C23*7,"")
=IF(H23<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(H23,1,3))+H23*7,"")

For Sundays

=IF(C9<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(C9,1,3))+C9*7+6,"")
=IF(H9<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(H9,1,3))+H9*7+6,"")
=IF(C23<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(C23,1,3))+C23*7+6,"")
=IF(H23<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(H23,1,3))+H23*7+6,"")

Now, if I enter 37 in C9, Monday and Sunday for week 37 are calculated correctly as 12th and 18th Sept, while week 38 and following are wrong (18th Sep and 24th Sep). Where is the error?

Upvotes: 0

Views: 106

Answers (1)

Mukul Varshney
Mukul Varshney

Reputation: 3141

There are few points for consideration w.r.t. =IF(C9<>"",DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(C9,1,3))+C9*7,"")

  1. Why we are extracting Weekday when we know its Monday i.e. 2.
  2. For Sunday we know its Monday + 6 days.

Probably you used the formula from the site.

=DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7 ---> Here B2 and A2 are different, while you are referring to same cell in your formula.

In your formula --- replace WEEKDAY(DATE(H9,1,3)) with WEEKDAY(DATE(YEAR(TODAY()), 1, 3)). It will work.

Upvotes: 1

Related Questions