| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for finding Date difference between 2 dates
Hi,
Please help me to find a formula for calculating the number of days between 2 dates excluding the dates given in a range. Networkdays function doesnt work as it excludes Saturday & sundays. Example: Cell A1 = 3-Mar-2009 Cell A2 = 15 March 2009 My Holiday list contains in Column Range E1:E5 E1 = 6-Mar-2009 E2 = 7-Mar-2009 E3 = 13-Mar-2009 E4 = 20-Mar-2009 E5 = 27-Mar-2009 Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days. Can Anuone help me please . I am desperately looking for an answer... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for finding Date difference between 2 dates
=A2-A1-SUMPRODUCT(--(E1:E5A1),--(E1:E5<A2))
Regards, Stefi €žSaji€ť ezt Ă*rta: Hi, Please help me to find a formula for calculating the number of days between 2 dates excluding the dates given in a range. Networkdays function doesnt work as it excludes Saturday & sundays. Example: Cell A1 = 3-Mar-2009 Cell A2 = 15 March 2009 My Holiday list contains in Column Range E1:E5 E1 = 6-Mar-2009 E2 = 7-Mar-2009 E3 = 13-Mar-2009 E4 = 20-Mar-2009 E5 = 27-Mar-2009 Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days. Can Anuone help me please . I am desperately looking for an answer... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for finding Date difference between 2 dates
Hi Stefi,
Thanks a lot.. This worked fine. I was blowing my mind with netwrokdays and I didnt reach anywhere. Thank you very much.. "Stefi" wrote: =A2-A1-SUMPRODUCT(--(E1:E5A1),--(E1:E5<A2)) Regards, Stefi €žSaji€ť ezt Ă*rta: Hi, Please help me to find a formula for calculating the number of days between 2 dates excluding the dates given in a range. Networkdays function doesnt work as it excludes Saturday & sundays. Example: Cell A1 = 3-Mar-2009 Cell A2 = 15 March 2009 My Holiday list contains in Column Range E1:E5 E1 = 6-Mar-2009 E2 = 7-Mar-2009 E3 = 13-Mar-2009 E4 = 20-Mar-2009 E5 = 27-Mar-2009 Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days. Can Anuone help me please . I am desperately looking for an answer... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for finding Date difference between 2 dates
=N(A2-A1-(COUNTIF(E1:E5,"="&A1)-COUNTIF(E1:E5,"="&A2)))
-- __________________________________ HTH Bob "Saji" wrote in message ... Hi, Please help me to find a formula for calculating the number of days between 2 dates excluding the dates given in a range. Networkdays function doesnt work as it excludes Saturday & sundays. Example: Cell A1 = 3-Mar-2009 Cell A2 = 15 March 2009 My Holiday list contains in Column Range E1:E5 E1 = 6-Mar-2009 E2 = 7-Mar-2009 E3 = 13-Mar-2009 E4 = 20-Mar-2009 E5 = 27-Mar-2009 Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days. Can Anuone help me please . I am desperately looking for an answer... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula for finding Date difference between 2 dates
Hi, Here are two more ways you coud use.
=DATEDIF(A1,A2,"d")-COUNTIF(E1:E5,"<"&A2)-COUNTIF(E6:E10,"<="&A1) or =DATEDIF(A1,A2,"d")-SUM(($E$1:$E$5=A1)*($E$1:$E$5<=A2)*1) The latter on is an array formula, entered with Control-Shift-Enter. Dave "Saji" wrote: Hi, Please help me to find a formula for calculating the number of days between 2 dates excluding the dates given in a range. Networkdays function doesnt work as it excludes Saturday & sundays. Example: Cell A1 = 3-Mar-2009 Cell A2 = 15 March 2009 My Holiday list contains in Column Range E1:E5 E1 = 6-Mar-2009 E2 = 7-Mar-2009 E3 = 13-Mar-2009 E4 = 20-Mar-2009 E5 = 27-Mar-2009 Answer should be 15 - 3 - (3 Holidaysfalls between the Dates )) = 9 days. Can Anuone help me please . I am desperately looking for an answer... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding if a date falls between two dates | Excel Discussion (Misc queries) | |||
| finding if a date falls between two dates | Excel Discussion (Misc queries) | |||
| What is a formula for finding the largest date in a list of dates? | Excel Discussion (Misc queries) | |||
| finding date difference | Excel Discussion (Misc queries) | |||
| Finding dates within a date range | Excel Worksheet Functions | |||
Hybrid Mode
