Extracting the date value form a formula for further use

[Drofidnas]

Hi

I have a formula =IF(B24=$I$1,"",C24) in D24 which basically interrogates the status in Col B and if it doesn't equal "Completed" (the contents of $I$1) will return the forecast date in Col C, formatted as mmm yy

       A            B               C             D
23
24 ABE At Validation 28/02/2014 Feb 14
25

Elsewhere on the sheet I have N2 as =NOW() formatted as mmm yy returning the current month and year.

I want to compare N2 and D24 but as they are formulaic values I'm only getting 0 as my result.
How do I extract the pure date out to compare them?

Thanks for any help.

Chris

 

[Megster]

Try the following formula:

=Month(C24)&Day(C24)=Month(N2)&Day(N2)

This will evaluate to TRUE if the months and years are the same in the two cells (C24 and N2)
I think the issue with C24=N2 is that N2 evaluates NOW() as a date and time.

 

[Peter_SSs]

.. or, assuming you only want to check month & year

=TEXT(D24,"mmyy")=TEXT($N$2,"mmyy")

 

[Drofidnas]

.. or, assuming you only want to check month & year

=TEXT(D24,"mmyy")=TEXT($N$2,"mmyy")

Thanks Peter that does just what I want.

Next problem though, I need to express that in a range (column D) If I use =COUNTIF(D2:D200,TEXT($N$2,"mmyy")) to give me a total of matching dates, where would I use TEXT for the range part?

 

[Peter_SSs]

Thanks Peter that does just what I want.

Good news.

Next problem though, I need to express that in a range (column D) If I use =COUNTIF(D2:D200,TEXT($N$2,"mmyy")) to give me a total of matching dates, where would I use TEXT for the range part?

I would probably swap to SUMPRODUCT for that:

=SUMPRODUCT(--(TEXT(D2:D200,"mmyy")=TEXT(N2,"mmyy")))


However, COUNTIFS could also be used:

=COUNTIFS(D2:D200,">"&N2-DAY(N2),D2:D200,"<="&EOMONTH(N2,0))

 

[Drofidnas]

Good news.


I would probably swap to SUMPRODUCT for that:

=SUMPRODUCT(--(TEXT(D2:D200,"mmyy")=TEXT(N2,"mmyy")))


However, COUNTIFS could also be used:

=COUNTIFS(D2:D200,">"&N2-DAY(N2),D2:D200,"<="&EOMONTH(N2,0))

Genius, sir, thank you.

I was on the COUNTIFS trail but had the syntax wrong. Many thanks for your help.

Chris

 

[Peter_SSs]

Glad it worked for you. Thanks for the feedback