F2 and F9 work, but nothing else

gr8whthunter76

New Member
Joined
Feb 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
    Capture.PNG
    72.7 KB · Views: 2

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,494
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try
Excel Formula:
=M2+0
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,494
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Feb 16, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
@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!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,913
Messages
5,627,601
Members
416,255
Latest member
amethystia

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top