# Excel Function to Produce Correct Date

#### cmschmitz24

##### Board Regular
Hello,

I need help with a function to populate the right date depending on what date another cell is:

I need to have either 10/1/"current year" or 10/1/"next year" populated in cell D60 based on what date cell B60 is populated.

If B60 is before 10/1/"current year", then I need to have 10/1/"current year" populated. If B60 is 10/1/"current year" or any date after 10/1, then I need to have 10/1/"next year" populated.

Ex:
B60 = 8/1/19, D60 = 10/1/2019
B60 = 10/15/19, D60 = 10/1/2020

Ex:
B60 = 1/15/19, D60 = 10/1/2019
B60 = 12/1/19, D60 = 10/1/2020

Thank you!
Christina

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### Joe4

Assuming the entry in cell B60 is a valid date, how about something like:
Code:
``=DATE(IF(MONTH(B60)>=10,YEAR(B60)+1,YEAR(B60)),10,1)``

The only question is what to return if B60 is a 10/1 date, i.e. 10/1/2019.
Do you want to return 10/1/2019 or 10/1/2020 in that case?

Last edited:

#### cmschmitz24

##### Board Regular
If B60 = 10/1/19, then D60 = 10/1/19

#### Joe4

Try this:
Code:
``=DATE(IF(OR(MONTH(B60)<10,TEXT(B60,"mmdd")="1001"),YEAR(B60),YEAR(B60)+1),10,1)``

#### cmschmitz24

##### Board Regular

This works perfect, thank you!

You are welcome.

#### cmschmitz24

##### Board Regular

Okay, so I thought I was smart enough to throw this into a nested IF/THEN statement considering there's one more condition, however I'm stuck...

IF(B9>=2658,DATE(IF(OR(MONTH(B60)<10,TEXT(B60,"mmdd")="1001"),YEAR(B60),YEAR(B60)+1),10,1), "Not Eligible")

I can't figure out how to break up the function you have created. Can you help?

Thanks!

#### Joe4

What value do you currently have in cell B9, what is your expected result of your formula, and what is the actual result you are getting?

Also, what does this formula return:
Code:
``=ISNUMBER(B9)``

Last edited:

#### cmschmitz24

##### Board Regular
The =ISNUMBER(B9) formula = TRUE
B9 is a manual entry so it will vary depending on the circumstance but it will always be a number (salary to be exact).

I'm getting a result of "Not Eligible" with the current formula I'm using if B9 is less than 2658, but I'm getting "275" if B9 is greater than or equal

I expect to get a result per the formula you had provided or if the value in cell B9 is less than 2658 then I expect to get a "Not Eligible" text.

#### cmschmitz24

##### Board Regular
NEVERMIND! I found what I wasn't doing, everything seems to be populating correctly!!
Thanks!

Replies
1
Views
60
Replies
7
Views
77
Replies
1
Views
45
Replies
19
Views
159
Replies
19
Views
158