# Extracting the date value form a formula for further use

#### Drofidnas

##### Board Regular
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

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

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

Last edited:
.. 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?

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))

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

Glad it worked for you. Thanks for the feedback

Replies
1
Views
188
Replies
5
Views
227
Replies
2
Views
197
Replies
1
Views
183
Replies
0
Views
363

1,221,010
Messages
6,157,362
Members
451,416
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back