If D22 equals "Weekly" then L22 Date+7

Sumeluar

Active Member
Joined
Jun 21, 2006
Messages
250
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hello all! D22=E5, E5 data validated to Daily, weekly, Monthly, .... So if E5="Weekly" (no quotes) thenD22=E5 therefore L22=Date+7. Basicaly I want L22 to add 1, 7, 14, 30, 120 days depending on what is selected on E5.

Is probably a beginers question, but being new to this doesn't help.

Any sugestions, much appreciated!

Regards!

Sumeluar
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
In L22
=TODAY() + HLOOKUP(E5,{"daily","weekly","fortnightly","monthly","triannualy";1,7,14,30,120},2,FALSE)
 
Upvote 0

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
In A1:B5

Daily 1
Weekly 7
Bi-Weekly 14
Monthly 30
Quarterly 120

use:
=TODAY()+VLOOKUP(F1,A1:B5,2,FALSE)
 
Upvote 0

Sumeluar

Active Member
Joined
Jun 21, 2006
Messages
250
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
You guys are awesome! Thank you, Thank you, Thank you for your quick and accurate response.

Regards!

Sumeluar
 
Upvote 0

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Using the HLookup - it doesn't seem to tolerate substituting for "daily", "weekly", etc
cell references such as $F$1, $F$2, etc where these contain "daily", (w/o the quotes).
Why is that?
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Using the HLookup - it doesn't seem to tolerate substituting for "daily", "weekly", etc
cell references such as $F$1, $F$2, etc where these contain "daily", (w/o the quotes).
Why is that?

Hello Jim,

HLOOKUP should work in exactly the same way as the VLOOKUP you proposed except the range would need to be transposed, e.g. if A1:E1 contained "daily", "weekly" etc and A2:E2 the numbers, then formula would be

=TODAY()+HLOOKUP(F1,A1:E2,2,FALSE)
 
Upvote 0

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Thanks Barry;
My question has to do with Mikerickson's proposed formula, as follows:

=TODAY() + HLOOKUP(E5,{"daily","weekly","fortnightly","monthly","triannualy";1,7,14,30,120},2,FALSE)

I tried substituting $F$1 for "daily", $F$2 for "weekly", etc.. but it wouldn't work.
That was my question.
Tks,

Jim
 
Upvote 0

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
From Excel Help "array constants"

"Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent sign)."

(Yet the array formula ={"$";")";"%"} worked fine for me)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

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