Remember Me?

 Straight Dope Message Board Remember Me?

#1
11-16-2006, 04:26 PM
 Carol the Impaler Charter Member Join Date: Mar 2003 Location: ibôápka Posts: 3,855
How can I get Excel to calculate the number of calendar days?

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?
#2
11-16-2006, 04:34 PM
 bordelond Guest Join Date: Dec 1999 Location: La Rive Ouest Posts: 9,275
Quote:
 Originally Posted by 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?
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.
#3
11-16-2006, 04:48 PM
 Carol the Impaler Charter Member Join Date: Mar 2003 Location: ibôápka Posts: 3,855
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.
#4
11-16-2006, 04:48 PM
 jsc1953 Guest Join Date: Aug 2000 Location: Bay Area, California Posts: 9,293
Quote:
 Originally Posted by 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:
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.
#5
11-16-2006, 04:50 PM
 jsc1953 Guest Join Date: Aug 2000 Location: Bay Area, California Posts: 9,293
Quote:
 Originally Posted by 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.
Try plugging those dates into bordelond's formula, and tell us what you get.
#6
11-16-2006, 04:54 PM
 Dervorin Guest Join Date: Jan 2006 Location: London Posts: 2,344
Try this:

=INT(DATEDIF(cell1, cell2, "d") + 1)
#7
11-16-2006, 04:55 PM
 Carol the Impaler Charter Member Join Date: Mar 2003 Location: ibôápka Posts: 3,855
Oops. Yeah, that worked.
#8
11-16-2006, 05:03 PM
 Carol the Impaler Charter Member Join Date: Mar 2003 Location: ibôápka Posts: 3,855
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.

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     General Questions     Great Debates     Elections     Cafe Society     The Game Room     Thread Games     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit

All times are GMT -5. The time now is 02:37 PM.

 -- Straight Dope v3.7.3 -- Sultantheme's Responsive vB3-blue Contact Us - Straight Dope Homepage - Archive - Top