Excel 2007 : Nested If Formula

Hi all,

I have 2 workbooks to merge. Workbook 1 is used for logging the data and workbook 2 is used for table and graphs.
Long story short, this is the formula I'm dealing with for workbook 1:

=IF((V7=$M$2),R7,0)

The value of V7 is a date with the following formula where B7 represents a past or present date:

=IF(ISBLANK(B7),0,BY-DAY(B7)+1)

And M2 is a date with the following formula where M1 is =NOW:

=M1-DAY(M1)+1

Everything works fine except the formula underlined above. I want the formula to be able to say if V7 is equal to (or the same) as M2, then it should equal data from R7, if it's not true, it should be 0. The formula looks logically correct to me (I'm not an Excel expert) but it's not posting the data from R7 when true.

The reason for the complexity of the formula and my workbook is because we will be collecting data continuously into the future, but need to run reports to capture month to date status. The second reason for the complexity is that I need this workbook to be formula driven as it will be a shared workbook used by other management who may not be as familiar with Excel (so all they need to do is enter data in workbook 1 and then run workbook 2 to print reports).

Thanks in advance for anyone who can provide any help! ☺

Hi Aeriuzi, welcome to the forum.

It looks like the value in B7 is a date (no time value), while the value in M2 is a date-time value because M1 is "=NOW()". Dates in Excel are whole numbers, and time values are decimals between 0 and 1. So 12:00 Noon today (Feb 23) is actually 40597.50 to Excel. If you're trying to compare 40597 to 40597.50, you're never going to get it to match and show the value in R7.

I'd suggest either wrapping the formula in M2 within an INT() function to get just the date portion of the date-time value, or, in the IF formula wrap INT around the M2 portion. You could also try using "=TODAY()" in M1 instead of "=NOW()".

=IF(V7=INT($M$2),R7,0)

Hope that helps!