Count if column A date larger than column B date by 30 days

[kwangyeow]

Hi Guys,

I am trying to count column M compare with column U if U - 30 days still larger than column M date, when column T is empty, and column C is not blank. Column C has formula return empty data.
Column U has to be date format, so i had enter ISTEXT to check for text and do not count if it is text. I would like to get the count using 1 formula instead of create another column to check every row date U1-30>M1.

=SUMPRODUCT(((U:U-M:M)>30)*(U:U<>"")*(T:T="")*(C:C<>"")*(ISTEXT(U:U)=FALSE))

However i am able to get conditional formatting formula for this purpose "=AND($C1<>"",(AND($T1="",(($U1-30)>$M1))))"

Thank you !

 

[Joe4]

It would be much easier to help you if we could copy your data sample, so we can easily work on the same set of data that you have.
Unfortunately, it looks like you posted an image, which does not allow us to copy and paste your data into Excel on our side.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kwangyeow]

Hi Joe, thank you for pointing out. I had link to this sample file in google drive : Link

 

[Joe4]

Hi Joe, thank you for pointing out. I had link to this sample file in google drive : Link

OK. I am unable to download files from the internet from my current location (I was have access later).
If you cannot upload it using the tools mentioned, maybe someone else will be able to download your file in the meantime.

 

[Joe4]

I do see a few issues which are causing the issues for you:

1. You are using entire column references in your formula, i.e. M:M, U:U, T:T, etc. However, you have blank rows and headers rows at the top. So using this method will not work well. You should exclude the header rows, and use specific range references, i.e. M7:M18, etc.

2. You are trying to perform mathematical computations on columns with mixed data (both numeric/date and text in it) in column U. This probably isn't going to work well. I would recommend addressing it in one of two ways:
- Create a User Defined Function in VBA on order to invidiually evaluate each row, and return the total count that you are looking (there may possibly be a complex formula you can use that does not require this, but I do not know what that would look like).
- Change it so that column U only contains date. Add another column for your text entries (i.e. "TBC"). Then we may be to come up with the straightforward formula that does what you want without having to use VBA.