# Date function

#### william11

##### New Member
My date (J4) is 1/15/2014

If billing frequency (H4) is 1, I want formula to add 1 year, every year to 1/15/2014
If billing frequency (H4) is 2, I want formula to add 6 months, every 6 months to 1/15/2014
If billing frequency (H4) is 4, I want formula to add 3 months, every 3 months to 1/15/2014

thank you.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I don't know where your formulas are going, but the easiest place would be to put it in J5-J(whatever). Then the formula is:

Code:
``=DATE(YEAR(J4),MONTH(J4)+(12/\$H\$4),DAY(J4))``

And you can drag it down.

I don't know where your formulas are going, but the easiest place would be to put it in J5-J(whatever). Then the formula is:

Code:
``=DATE(YEAR(J4),MONTH(J4)+(12/\$H\$4),DAY(J4))``

And you can drag it down.

I am sorry I thought a previous formula I had received on this site was messed up but it turns out my dates were incorrect.

=IF(J4>TODAY(),EDATE(J4,12/H4),EDATE(J4,DATEDIF(J4,TODAY(),"m")+12/H4-(MOD(DATEDIF(J4,TODAY(),"m"),12/H4))))

Is what I was trying to do for the record.

Last edited:
In J5:
=IF(COUNT(\$J\$4:J4)<=\$H\$4,EDATE(J4,12/\$H\$4),"")

Copy down to J8.

Replies
18
Views
268
Replies
4
Views
206
Replies
13
Views
253
Replies
12
Views
397
Replies
7
Views
312

1,196,250
Messages
6,014,244
Members
441,808
Latest member
xplainer

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