| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Subracting Dates to come up with the # of days between them
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 :) |
|
#2
|
|||
|
|||
|
Hi
then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#3
|
|||
|
|||
|
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" wrote: 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 :) |
|
#4
|
|||
|
|||
|
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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#5
|
|||
|
|||
|
Hi
you know that 2004 is a leap year? so 366 is correct :-) -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#6
|
|||
|
|||
|
Kimberly
Are you forgetting that 2004 is a Leap Year and has 29 days in February? Gord Dibben Excel MVP On Mon, 20 Dec 2004 11:31:13 -0800, "KimberlyC" wrote: 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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#7
|
|||
|
|||
|
Use
=MAX(B1-A1+1,365) Regards, Peo Sjoblom "KimberlyC" wrote: 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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#8
|
|||
|
|||
|
Hi Peo
that's a nice workaround :-)) -- Regards Frank Kabel Frankfurt, Germany "Peo Sjoblom" schrieb im Newsbeitrag ... Use =MAX(B1-A1+1,365) Regards, Peo Sjoblom "KimberlyC" wrote: 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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
|
#9
|
|||
|
|||
|
Oh Dear! :)
I did forget!! Thanks so much for you help... "Frank Kabel" wrote in message ... Hi you know that 2004 is a leap year? so 366 is correct :-) -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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" wrote in message ... Hi then use =DATEDIF(C10,D10,"D")+1 -- Regards Frank Kabel Frankfurt, Germany "KimberlyC" schrieb im Newsbeitrag ... 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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
| due dates | New Users to Excel | |||
| How do I find how many business days are between two dates | Excel Worksheet Functions | |||
| Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions | |||
| Difference between two dates in months with decimals | Excel Worksheet Functions | |||
Linear Mode
