The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 11-16-2006, 03:26 PM
Carol the Impaler Carol the Impaler is offline
Charter Member
 
Join Date: Mar 2003
Location: ibpka
Posts: 3,809
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?
Reply With Quote
Advertisements  
  #2  
Old 11-16-2006, 03:34 PM
bordelond bordelond is offline
Guest
 
Join Date: Dec 1999
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.
Reply With Quote
  #3  
Old 11-16-2006, 03:48 PM
Carol the Impaler Carol the Impaler is offline
Charter Member
 
Join Date: Mar 2003
Location: ibpka
Posts: 3,809
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.
Reply With Quote
  #4  
Old 11-16-2006, 03:48 PM
jsc1953 jsc1953 is offline
Guest
 
Join Date: Aug 2000
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.
Reply With Quote
  #5  
Old 11-16-2006, 03:50 PM
jsc1953 jsc1953 is offline
Guest
 
Join Date: Aug 2000
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.
Reply With Quote
  #6  
Old 11-16-2006, 03:54 PM
Dervorin Dervorin is offline
Guest
 
Join Date: Jan 2006
Try this:

=INT(DATEDIF(cell1, cell2, "d") + 1)
Reply With Quote
  #7  
Old 11-16-2006, 03:55 PM
Carol the Impaler Carol the Impaler is offline
Charter Member
 
Join Date: Mar 2003
Location: ibpka
Posts: 3,809
Oops. Yeah, that worked.
Reply With Quote
  #8  
Old 11-16-2006, 04:03 PM
Carol the Impaler Carol the Impaler is offline
Charter Member
 
Join Date: Mar 2003
Location: ibpka
Posts: 3,809
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.
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

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 Jump


All times are GMT -5. The time now is 03:02 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.