Hello all,
This is my first time posting on here but most definitely not the first time receiving your help as I usually am directed here in my google searches
. Please forgive me if this was solved previously but I have not seen it in my search.
Note: Using Excel 2007
I need to find a way to count the amount of time something remains NOT closed and was submitted prior to a month that is in the heading (that the user will fill) The problem I am having, is that the column that contains the dates that excel searches through, are put together from other cells - not entered as text.
The people I need to submit this to have mentioned that they would prefer as much as possible not as macros so that at a later date when I am unavailable they will still be able to look at it and understand the work.
I have tried =COUNTIFS(W$16:W$25,"<"&VALUE(G$29)) But this only works with dates as typed in and not the dates that are made by referring to other cells as I currently must have them (I had to convert Euro Dates to American dates by formulae)
I need to find the # of cells with Status (column H in restorations sheet) Not = Closed, AND has an American Submission Date (Column O in Restorations Sheet that contains a formula to build the date: e.g. =IF(ISERROR(MONTH($E2)),AD2,TEXT($E2,"dd/mm/yyyy"))) that is prior to the first of the month that is entered into cell B2 (of the Summary sheet)
I hope this is enough information. Thank you!
This is my first time posting on here but most definitely not the first time receiving your help as I usually am directed here in my google searches
. Please forgive me if this was solved previously but I have not seen it in my search. Note: Using Excel 2007
I need to find a way to count the amount of time something remains NOT closed and was submitted prior to a month that is in the heading (that the user will fill) The problem I am having, is that the column that contains the dates that excel searches through, are put together from other cells - not entered as text.
The people I need to submit this to have mentioned that they would prefer as much as possible not as macros so that at a later date when I am unavailable they will still be able to look at it and understand the work.
I have tried =COUNTIFS(W$16:W$25,"<"&VALUE(G$29)) But this only works with dates as typed in and not the dates that are made by referring to other cells as I currently must have them (I had to convert Euro Dates to American dates by formulae)
I need to find the # of cells with Status (column H in restorations sheet) Not = Closed, AND has an American Submission Date (Column O in Restorations Sheet that contains a formula to build the date: e.g. =IF(ISERROR(MONTH($E2)),AD2,TEXT($E2,"dd/mm/yyyy"))) that is prior to the first of the month that is entered into cell B2 (of the Summary sheet)
I hope this is enough information. Thank you!
