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?
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.
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.
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.
Try plugging those dates into bordelond’s formula, and tell us what you get.
Try this:
=INT(DATEDIF(cell1, cell2, “d”) + 1)
Oops. Yeah, that worked.
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.