Date (Month/Year) Comparison. Nested IFs

[dave85118]

Hello, I am new to the forum. I've been searching Google the past 2 days, but can't seem to get this working.


I am using a table, with Nov 2017 - December 2018 in as table headers.
I have another column of Est. Closed Date and Net Revenue.


I am trying to right a formula that says:


IF Est. Close Date is EQUAL to Header Date, then display Net Revenue times 50%
BUT
IF Est. Close Date is LESS THAN Header Date, display 0.00
ELSE
If Est. Close Date is GREATER THAN Header Date, display Net Revenue.


I can get it to work for my first column, November 2017, but it doesn't seem to work for the other months. I assume I have to do something special to compare months? I've tried nested IF statements, IFS, and even multiple IF, but I just can't get it. I would include a screenshot, but


Working formula for Nov: =IF(MONTH([@[Est''d Close Date]])&YEAR([@[Est''d Close Date]])=MONTH(Table5[[#Headers],[11/1/2017]])&YEAR(Table5[[#Headers],[11/1/2017]]),[@[Final Net Monthly Revenue]]*0.5,IF([@[Est''d Close Date]]<Table5[[#Headers],[11/1/2017]],0,[@[Final Net Monthly Revenue]]))


Here is a screenshot. Thank you so much to anyone that can help.

 

[Special-K99]

Re: Date (Month/Year) Comparison. Nested IFs HELP

It's not clear if those dates are proper dates or text.
This should work regardless.

in V4
=$U4*LOOKUP(SIGN((("01-"&TEXT(U$3,"mm-yy"))+0)-(("01-"&TEXT($S4,"mm-yy"))+0)),{-1,0,1},{0,0.5,1})

copy across to AI4 and down

Method:
Subtract the date in the heading from the date in S4
Obtain the sign of that calculation (-1, 0, 1)
Use a LOOKUP of that calculation to return the factor (0, 0.5, 1) by which to multiply U4.

 

[dave85118]

Re: Date (Month/Year) Comparison. Nested IFs HELP

Do you have suggestion of how the dates should be formatted? I've tried a few ways. I still think the dates are what is causing our issue, like it is only verifying the months or something. Here is the screenshot from row 4 & 5 updated with your formula.

 

[Special-K99]

Re: Date (Month/Year) Comparison. Nested IFs HELP

I can't reproduce that error either with text or proper dates.
Could you upload the file to an online site and post a link here?
(The mods usually go Gestapo if I ask for a file straight away. Apparently we must ask for screenshots first (which is not usually helpful) THEN an upload of a file but since you've already posted screenshots I don't feel I'm obliged to do so especially when we are trying to establish the actual type of data that is causing the problem).

You might be able to Private Message me with the file but I'm not sure if this forum supports that.

 

[dave85118]

Re: Date (Month/Year) Comparison. Nested IFs HELP

I made a new sheet with minimal content, still not having the best of luck. I've uploaded the sheet to Google drive. https://goo.gl/ed6fNk [FONT=Roboto, Helvetica, Arial, sans-serif] . I am VERY thankful for your help Special-K99![/FONT]

 

[Special-K99]

Re: Date (Month/Year) Comparison. Nested IFs HELP

Ah that's cleared it up.
The dates in column A are internal Excel dates, ie numbers but the dates in row 1 are text. So we only need to convert the dates on row 1.

Try this

in D2
=$C2*LOOKUP(SIGN(((TEXT("01-"&D$1,"mm-yy"))+0)-$A2),{-1,0,1},{0,0.5,1})
and copy right and down

 

[dave85118]

Re: Date (Month/Year) Comparison. Nested IFs HELP

Working now! You have been great, I truly appreciate all of your help.