Excel Function to Produce Correct Date

cmschmitz24

Board Regular
Joined
Jan 27, 2017
Messages
150
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
Upvote 0
Try this:
Code:
=DATE(IF(OR(MONTH(B60)<10,TEXT(B60,"mmdd")="1001"),YEAR(B60),YEAR(B60)+1),10,1)
 
Upvote 0
You are welcome.
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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
Back
Top