If Statement Date comparison, Month count

[PantherTheCat]

I have been trying to no avail, to be able to find out, how long an account has been active within the time frame of a certain year.

I created a formula to change all the start dates to 1/1/2010

I now need to find out if they had an end date, and if so, how many months they were active in a year.

Here is some examples of what the sheet looks like .


1/1/2010 9/9/2010 9/10/2010 8
1/1/2010 9/9/2010 9/10/2010 8
10/26/2010 10/30/2010 #NUM!
10/26/2010 11/1/2010 #NUM!
10/26/2010 11/1/2010 #NUM!
1/1/2010 2/1/2009 #NUM!
6/15/2010 10/12/2010 #NUM!
1/1/2010 #NUM!

Here is the formula i am using that is returning Dates
=IF(AND(DATEDIF(E183,F183,"m")<=11,(DATEDIF(E183,F183,"m")<>"")),(DATEDIF(E183,F183,"m")),(0))

Which clearly isnt working - If you notice the #num!

I also tried a formula to compare that to an Error.type 6

That did not work.

Ultimately if there is an easier way to take all the dates, determine between 4 columns How many months the site has been active in 2010, that would be great.

Column 1
Site Active in 2010
Column 2
Old software deactivated in 2010
Column 3
New Software Activated in 2010
Column 4
New Software deactivated in 2010

Everything but Column 1 has the possibility of not having a date in it. Which throws off the entire deal.

I have been trying to figure this out the past 2 days. Honestly by now I could have just manually done the calculations by hand - but i have a feeling that this will come up again and would love some insight.

 

[AlphaFrog]

Hi and welcome. In your example data, it's hard to tell what columns each data element belongs to. Because of that, it's hard to follow what you are calculating.

If you could paste your example data using one of these add-ins...
MrExcel HTML Maker or Excel Jeanie
...that would help a lot.

I'm not sure I follow what you are asking, but here's a guess with some made-up data.

Excel Workbook
	A	B	C	D	E
1	Start Old	End Old	Start New	End New	Months in 2010
2	1/1/2009	9/9/2009	4/10/2010	9/11/2010	5
3	2/1/2010	4/2/2010	4/3/2010	1/4/2011	10
4			3/3/2010	6/4/2010	3
5	2/1/2009	4/2/2010			3
6	1/1/2009	9/9/2009	1/10/2011	3/22/2011	0
Sheet

 

[PantherTheCat]

I apologize for not utilizing the correct tool.

And yes that is EXACTLY what I am talking about, your example graph appears just as I want the data to appear.

 

[PantherTheCat]

Okay, it did not work, the dates that had good ranges came back with #num!

Also i dont think the formula allows for multiple blanks

Such as a Start Date of 2/3/2009 and then no other dates (Meaning that it has been active and not upgraded)

Now i filled in that if they had Anything in Column A and nothing in any other coumn to DATEDif((a1),Date(2011,1,1),"m")