Excel Function to Produce Correct Date

[cmschmitz24]

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

 

[Joe4]

Assuming the entry in cell B60 is a valid date, how about something like:

=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?

 

[cmschmitz24]

If B60 = 10/1/19, then D60 = 10/1/19

 

[Joe4]

Try this:

=DATE(IF(OR(MONTH(B60)<10,TEXT(B60,"mmdd")="1001"),YEAR(B60),YEAR(B60)+1),10,1)

 

[cmschmitz24]

This works perfect, thank you!

 

[Joe4]

You are welcome.

 

[cmschmitz24]

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:

=ISNUMBER(B9)

 

[cmschmitz24]

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]

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