# F2 and F9 work, but nothing else

#### gr8whthunter76

##### New Member
Ok, I am a excel simpleton, but generally I can get things to work. So in Q2 I have this formula =IF(AND(\$O2>=\$C2,\$O2<\$D2),A2,IF(AND(\$O2>=\$C3,\$O2<\$D3),A3,IF(AND(\$O2>=\$C4,\$O2<\$D4),A4,IF(AND(\$O2>=\$C5,\$O2<\$D5),A5,IF(AND(\$O2>=\$C6,\$O2<\$D6),A6,IF(AND(\$O2>=\$C7,\$O2<\$D7),A7,IF(AND(\$O2>=\$C8,\$O2<\$D8),A8,IF(AND(\$O2>=\$C9,\$O2<\$D9),A9,IF(AND(\$O2>=\$C10,\$O2<\$D10),A10,IF(AND(\$O2>=\$C11,\$O2<\$D11),A11,IF(AND(\$O2>=\$C12,\$O2<\$D12),A12,IF(AND(\$O2>=\$C13,\$O2<\$D13),A13,IF(AND(\$O2>=\$C14,\$O2<\$D14),A14,IF(AND(\$O2>=\$C15,\$O2<\$D15),A15))))))))))))))
Which then if I put a date in O2 it bumps it against a chart I have and gives me the year I need. That works perfectly. The issue is when I try to automate it more. I put EOD:12/01/2019 in K2 and in M2 I use the formula =RIGHT(\$K2,LEN(\$K2)-SEARCH(":",\$K2)) to give me the answer of 12/01/2019 in M2. What I want to do is use that answer from M2 be able to put it in O2 and have my formula in Q2 work. I used in O2 =m2 which gives me the date just fine, but doesn't do anything to Q2 which stays as false. NOW if I click on O2 and hit the old fashioned F2 F9 it converts =m2 to a value and all is well. I have tried copy and paste options and that doesn't work either. So confused.

#### Attachments

• Capture.PNG
72.7 KB · Views: 2

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### gr8whthunter76

##### New Member
Better Image, sorry

#### Attachments

• Capture.PNG
76.6 KB · Views: 1

#### RoryA

##### MrExcel MVP, Moderator
Try
Excel Formula:
``=M2+0``

#### RoryA

##### MrExcel MVP, Moderator
FYI, as it appears your years always incept on the 1st October, there are simpler formulas, like:

=YEAR(O2)-(MONTH(O2)<10)

or you could use a simple LOOKUP formula.

#### gr8whthunter76

##### New Member
@RoryA 1) thank you that worked swimmingly, 2) the long formula I used was actually a formula from something else that i had done. Basically what I accomplishing in the long formula is that if a date falls between a fiscal year dates then I want that year, so 10/07/2020, what fiscal year is that in, 2021. Didn't know any other way to do it I guess. I know enough about excel to be dangerous, but all these nuances in the software, ya beyond me. I am just a simple Minnesota guy lol. THANK YOU!!!!!!!

Replies
7
Views
109
Replies
13
Views
384
Replies
3
Views
141
Replies
1
Views
60
Replies
14
Views
255

1,127,732
Messages
5,626,566
Members
416,190
Latest member
plee3

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