Calculating Time Period in Excel

[KellyStee]

I have a period of time (say from 1/1/2001 through 12/31/2001) and when I subtract the earlier date from the later one, I want the result to be 364. But, the result is actually 364 because 1/1 is not counted as part of the time period (under basic subtraction rules). An easy way to fix this would be to just add one or subtract 1/1/2001-1/1/2001, but my clients tend to be think that I'm including 1/1/2002 in the period also. Is there a date function resolves this issue? Does anyone have an easier solution?

Thanks in advance!!
Kelly

 

[JVFriederick]

Sounds pretty easy to me!

Within the formula, the fraction of each date, time, is assumed to be the same. So as you say, basic subtraction rules apply. If this will always be true, that the time of day is not considered, the formula calculates the correct number of days.

Tough clients !!

 

[KellyStee]

Oops. I guess I should review my messages before I post them. Actually, I WANT the result to be 365 not 364, and Excel always returns 364. How do I get it to include the start day of 1/1/2001 without adding one?

Sorry about that.
Kelly

 

[Dreamboat]

Um. How about =(Date-Date)+1

?
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[JVFriederick]

Ditto.

 

[KellyStee]

Thanks, guys, for your responses. + 1 is definitely an easy fix. I was just thinking that for getting a time period it could really throw a user off because it doesn't count the first day. It threw me off when a client kept telling me when she counted the days, she got 1 more than me. It doesn't seem like Excel has special formulas for this. Lesson learned: watch out for date formulas.

 

[Dreamboat]

I think the problem is, Kelly, that there really are only 364 full days between those two dates. Excel accounts for leap years too, I believe. The diff between 1/1/2001 and 1/1/2002 would be accurate.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[guava65]

In your example, 365 days do not actually pass until the stroke of 00:00 01/01/2002. The calculation is correct. the calculation counts days between the two dates. If you want to count both past and the current day, you have to increment the end date to the next day. Mahalo,
cg