# How can I get Excel to calculate the number of calendar days? **URL:** \ **Category:** Factual Questions\ **Created:** [November 16, 2006, 9:26pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737 "2006-11-16T21:26:53Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Carol\_the\_Impaler](https://avatars.discourse-cdn.com/v4/letter/c/6a8cbe/32.png) [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)\ **Post date:** [November 16, 2006, 9:26pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/1 "2006-11-16T21:26:53Z")
I’m trying to calculate the number of calendar days between two dates in Excel. Unfortunately, Excel seems to default to a 360-day calendar, so when my start date is (for example) October 1, 2006 and my end date is October 31, 2006 Excel returns the number of days as 30. I need it to return the number of days as 31. How do I do this? ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [November 16, 2006, 9:34pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/2 "2006-11-16T21:34:24Z")
[QUOTE=niblet\_head] I’m trying to calculate the number of calendar days between two dates in Excel. Unfortunately, Excel seems to default to a 360-day calendar, so when my start date is (for example) October 1, 2006 and my end date is October 31, 2006 Excel returns the number of days as 30. I need it to return the number of days as 31. How do I do this? [/QUOTE] I’m confused – is the difference not 30 days? In any case, you could enter a function in that field. Where B2 = 10/31/2006 and A2 = 10/1/2006: =(B2-A2)+1 Make sure that the formula field is designated a Number datatype, not Date. ---
**Author:** ![Carol\_the\_Impaler](https://avatars.discourse-cdn.com/v4/letter/c/6a8cbe/32.png) [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)\ **Post date:** [November 16, 2006, 9:48pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/3 "2006-11-16T21:48:07Z")
Unfortunately, that won’t work (adding 1) because my boss needs to be able to also, say, enter November 1 and November 30 and have it equal 30 days. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [November 16, 2006, 9:48pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/4 "2006-11-16T21:48:14Z")
[QUOTE=bordelond] I’m confused – is the difference not 30 days? In any case, you could enter a function in that field. Where B2 = 10/31/2006 and A2 = 10/1/2006: [/QUOTE] Just a different way of defining the problem. If you start a task on 10/1 and end on 10/31, how many days did you work? A: 31. In any case, it has nothing to do with a “360 day year” (??) and your formula (adding +1 to the difference) will always work. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [November 16, 2006, 9:50pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/5 "2006-11-16T21:50:33Z")
[QUOTE=niblet\_head] Unfortunately, that won’t work (adding 1) because my boss needs to be able to also, say, enter November 1 and November 30 and have it equal 30 days. [/QUOTE] Try plugging those dates into bordelond’s formula, and tell us what you get. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [November 16, 2006, 9:54pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/6 "2006-11-16T21:54:00Z")
Try this: =INT(DATEDIF(cell1, cell2, “d”) + 1) ---
**Author:** ![Carol\_the\_Impaler](https://avatars.discourse-cdn.com/v4/letter/c/6a8cbe/32.png) [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)\ **Post date:** [November 16, 2006, 9:55pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/7 "2006-11-16T21:55:34Z")
Oops. Yeah, that worked. ---
**Author:** ![Carol\_the\_Impaler](https://avatars.discourse-cdn.com/v4/letter/c/6a8cbe/32.png) [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)\ **Post date:** [November 16, 2006, 10:03pm UTC](https://boards.straightdope.com/t/how-can-i-get-excel-to-calculate-the-number-of-calendar-days/380737/8 "2006-11-16T22:03:32Z")
I take it back. \*\*Dervorin’s \*\* formula resulted in what I wanted. (There’s more to the formula. I need to also multiply the number of calendar days times the number of hours between 6am and midnight times the number of units we have available each hour. This is for radio rates, btw.) Using DATE360 plus 1 didn’t get me the correct number when I added the rest of the formula, but **Dervorin’s** version did. As an aside, I hope the SDMB never ever gets blocked by my IT department. If it does just working on this damn spreadsheet alone is three instances where I’ve used you all for work purposes.