=INT((B2-(B1+B3-WEEKDAY(B1)+7))\/7)+1+IF(WEEKDAY(B1)=B3,1,0)<\/pre>\nCorrectly returns 17.\u00a0 If we were to change B1 to 1\/5 (a Sunday), the result would be 16 without the extra IF term.<\/p>\n
<\/p>\n
For a more detailed explanation…<\/p>\n
<\/p>\n
Quick Background on Excel Dates<\/h2>\n
Excel stores a date as a “dateserial”… an integer number of days since 1\/1\/1900.<\/p>\n
So today’s dateserial (2\/20\/25) is 45,708.<\/p>\n
If I add one, 45,708 + 1 = 45,709 = 2\/21\/2025<\/p>\n
Time is a decimal value between integers.<\/p>\n
For example, 5 PM = 1700 hours.\u00a0 So 2\/20\/2025 + (17\/24) = 45,708.7083<\/p>\n
To add days, you add an integer number.\u00a0 2\/20\/2025 + 5 = 2\/25\/2025<\/p>\n
To find the number of days between the two, just subtract 2\/25\/2025 – 2\/20\/2025 = 5.<\/p>\n
<\/p>\n
Detailed Explanation<\/h2>\n
The first thing we need to do is find the first Sunday within the range.<\/p>\n
The WEEKDAY function returns a number 1-7 indicating the day of the week for a given date, where 1=Sunday, etc.<\/p>\n
If we subtract B3 (the day we are looking for) from the weekday of the start date, we get the number of days offset from the start date.<\/p>\n
=WEEKDAY(B1)<\/pre>\nReturns “4” because 1\/1\/2025 is on a Wednesday<\/p>\n
=B3-WEEKDAY(B1)<\/pre>\nReturns -3, which is an offset. If we go BACK 3 days from 1\/1, that’s the last Sunday in Dec, 2024.<\/p>\n
=B3-WEEKDAY(B1)+7<\/pre>\nBy adding 7, we ensure that we always have a positive offset from the start date (B1).<\/p>\n
=B1+B3-WEEKDAY(B1)+7<\/pre>\nIn this case, -3+7 = 4, and sure enough, B1 (start date) is on a Wednesday, if we add 4 days to B1, we get 1\/5, which is in fact the first Sunday on or after B1.<\/p>\n
Now that we have identified the correct starting point, we can simply subtract from the end date to get the total number of days.<\/p>\n
=B2-(B1+B3-WEEKDAY(B1)+7)<\/pre>\nReturns 116.\u00a0 Since =B2-B1 returns 120, this checks out…\u00a0 Remember that our offset to Sunday was 4 days.<\/p>\n
Since every Sunday is exactly 7 days from the previous, we can simply divide by 7.<\/p>\n
=INT((B2-(B1+B3-WEEKDAY(B1)+7))\/7)<\/pre>\nReturns 16.\u00a0 We use the INT function to return an integer…we don’t care about the fractional number of days between the last Sunday and the end date.<\/p>\n
However, we have a problem… there are\u00a017<\/strong> Sundays between the two.\u00a0 Remember that we subtracted (and therefore excluded) the first Sunday…<\/p>\n=INT((B2-(B1+B3-WEEKDAY(B1)+7))\/7)+1<\/pre>\nReturns 17.\u00a0 We can simply add 1 to put back the first Sunday that we subtracted.<\/p>\n
We can confirm this by changing B2 to 5\/4\/2025 (a Sunday), and the same formula correctly returns 18<\/strong>.<\/p>\nCAVEAT:<\/strong>\u00a0 If our starting date is also Sunday, for example 1\/5, the formula reports that there are only 17 Sundays between 1\/5 and 5\/4.\u00a0 This is technically correct, as now the first Sunday is no longer BETWEEN the two dates.\u00a0 However, this might not be the behavior you expect.\u00a0 If not, just add an IF:<\/p>\n=INT((B2-(B1+B3-WEEKDAY(B1)+7))\/7)+1+IF(WEEKDAY(B1)=B3,1,0)<\/pre>\nThe nice thing about this formula is that we can count any weekday we want.\u00a0 For example, we can count Fridays by changing B3 to 6<\/strong>.<\/p>\nI hope you find this useful, and happy Excelling!<\/p>\n","protected":false},"excerpt":{"rendered":"
Excel – Number of Sundays – A better Answer I looked up how to count the number of Sundays between two dates…. the so-called answer shocked me.\u00a0 There are a couple of popular answers, but they are horribly inefficient. Let me show you a better way….. Update 4\/22\/2025 Due to a severe copy and paste […]<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-7568","post","type-post","status-publish","format-aside","hentry","category-other-stuff","post_format-post-format-aside"],"_links":{"self":[{"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/posts\/7568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/comments?post=7568"}],"version-history":[{"count":10,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/posts\/7568\/revisions"}],"predecessor-version":[{"id":7588,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/posts\/7568\/revisions\/7588"}],"wp:attachment":[{"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/media?parent=7568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/categories?post=7568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/justinparrtech.com\/JustinParr-Tech\/wp-json\/wp\/v2\/tags?post=7568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}