Microsoft Office Forums Formula to return a value of 1,2,3 or based on a set criteria
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-01-2017, 12:00 PM
 Highlander01 Windows 10 Office 2016 Novice Join Date: Jan 2016 Location: Portsmouth, England Posts: 18
Formula to return a value of 1,2,3 or based on a set criteria

Dear Experts

Help!

I need to introduce a set of criteria in my spread sheet (Office 2010) based on a date.

I have a date in cell L5 and want to return a value of 1,2,3 or 4 in M5 based on the following criteria:
<4 months to return a value of 1
=>4 – <6 months to return a value of 2
=>6 – <12 months to return a value of 3
=>12 months to return a value of 4

Any help would be greatly appreciated

Gordon
#2
06-01-2017, 12:59 PM
 NBVC Windows 7 64bit Office 2007 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 215

Does this work?

=LOOKUP(DATEDIF(TODAY(),L5,"m"),{0,4,6,12},{1,2,3, 4})
#3
06-01-2017, 01:05 PM
 Highlander01 Windows 10 Office 2016 Novice Join Date: Jan 2016 Location: Portsmouth, England Posts: 18

WOW! Thank you so much.

I will give it a go tomorrow.

Gordon
#4
06-01-2017, 01:16 PM
 NBVC Windows 7 64bit Office 2007 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 215

No Problem.

If the dates in L5 are before today, then switch the L5 and TODAY() parameters...
#5
06-01-2017, 01:24 PM
 Highlander01 Windows 10 Office 2016 Novice Join Date: Jan 2016 Location: Portsmouth, England Posts: 18

The dates in L5 will definitely be before today, and can be anything between 3 and >12 months.

Just so I don't mess it up how would it look if I changed the parameters?

Gordon
#6
06-01-2017, 01:56 PM
 NBVC Windows 7 64bit Office 2007 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 215

Like this:

=LOOKUP(DATEDIF(L5,TODAY(),"m"),{0,4,6,12},{1,2,3, 4})
#7
06-01-2017, 02:18 PM
 Highlander01 Windows 10 Office 2016 Novice Join Date: Jan 2016 Location: Portsmouth, England Posts: 18

Excellent, thank you.
#8
06-02-2017, 01:17 PM
 Highlander01 Windows 10 Office 2016 Novice Join Date: Jan 2016 Location: Portsmouth, England Posts: 18

Quote:
 Originally Posted by NBVC Like this: =LOOKUP(DATEDIF(L5,TODAY(),"m"),{0,4,6,12},{1,2,3, 4})
I tried this today and hey presto. Thank you very much.

Appreciated!

Gordon

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post kwilson307 Excel 1 04-20-2017 05:07 AM phillipsdp Excel 1 11-09-2016 12:53 AM gebobs Excel 4 07-21-2016 08:26 AM oswald Excel 1 02-08-2015 02:01 AM SaneMan Excel Programming 2 02-02-2012 07:58 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:39 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top