# If D22 equals "Weekly" then L22 Date+7

#### Sumeluar

##### Active Member
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
In L22
=TODAY() + HLOOKUP(E5,{"daily","weekly","fortnightly","monthly","triannualy";1,7,14,30,120},2,FALSE)

#### jim may

##### Well-known Member
In A1:B5

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

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

#### Sumeluar

##### Active Member
You guys are awesome! Thank you, Thank you, Thank you for your quick and accurate response.

Regards!

Sumeluar

#### jim may

##### Well-known Member
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?

#### barry houdini

##### MrExcel MVP
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)

#### jim may

##### Well-known Member
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

#### mikerickson

##### MrExcel MVP
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:

Replies
6
Views
151
Replies
7
Views
314
Replies
0
Views
520
Replies
1
Views
66
Replies
1
Views
190

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.

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