Subracting Dates to come up with the # of days between them

[KimberlyC]

Hi

I have a "From" Date in Col C and and "To" Date in Col D.
In Col E, I have entred the following formula to calc. the number of days
between them =DATEDIF(C10,D10,"D")
( I'm trying to find the number of days each employee worked for the year
( weekends do not matter..they are supposed to be included in this total
number))

If I have (for ex) 11/1/2004 in C10 and 12/1/2004 in D10, the answer I get
in E10 = 30.
The days should be 31 ( 30 days in Nov and 1 day in Dec).

Is there a different formula I should be using to get the correct days I'm
looking for.....


Thanks in advance for your help!!
Kimberly

 

[Frank Kabel]

Hi
then use
=DATEDIF(C10,D10,"D")+1

 

[Peo Sjoblom]

Not that it makes any difference but since you want to count days you might
as well use

=D10-C10 format as general

and since you want to add one day use

=D10-C10+1

Regards,

Peo Sjoblom

 

[KimberlyC]

Hi,
Thanks...
This works, but when I have 1/1/2004 to 12/31/2004...the days are coming up
to 366 instead of 365
But the 11/1/2004 to 12/1/2004 comes up with 31 days..which is correct.
It appears the formula works correctly for the month to month dates...(like
11/1/2004 to 12/30/2004)...but it adds one to many days on the calendar year
dates
such as 1/1/2004 to 12/31/2004.
If I enter 4/1/2004 to 3/31/2005... I come up with 365.. so it appears it
only happens when it's the cal. year.???

Not sure how to resolve that one..
Any advise would be greatly appreicated.

 

[Frank Kabel]

Hi
you know that 2004 is a leap year? so 366 is correct

 

[Gord Dibben]

Kimberly

Are you forgetting that 2004 is a Leap Year and has 29 days in February?

Gord Dibben Excel MVP

 

[Peo Sjoblom]

Use

=MAX(B1-A1+1,365)

Regards,

Peo Sjoblom

Hi,
Thanks...
This works, but when I have 1/1/2004 to 12/31/2004...the days are coming up
to 366 instead of 365
But the 11/1/2004 to 12/1/2004 comes up with 31 days..which is correct.
It appears the formula works correctly for the month to month dates...(like
11/1/2004 to 12/30/2004)...but it adds one to many days on the calendar year
dates
such as 1/1/2004 to 12/31/2004.
If I enter 4/1/2004 to 3/31/2005... I come up with 365.. so it appears it
only happens when it's the cal. year.???

Not sure how to resolve that one..
Any advise would be greatly appreicated.

 

[Frank Kabel]

Hi Peo
that's a nice workaround )

 

[KimberlyC]

Oh Dear!
I did forget!!
Thanks so much for you help...