Excel Function to Produce Correct Date

cmschmitz24

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

Some videos you may like

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
Try this:
Code:
=DATE(IF(OR(MONTH(B60)<10,TEXT(B60,"mmdd")="1001"),YEAR(B60),YEAR(B60)+1),10,1)
 

cmschmitz24

Board Regular
Joined
Jan 27, 2017
Messages
109

ADVERTISEMENT

This works perfect, thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

cmschmitz24

Board Regular
Joined
Jan 27, 2017
Messages
109

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 27, 2017
Messages
109
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
Joined
Jan 27, 2017
Messages
109
NEVERMIND! I found what I wasn't doing, everything seems to be populating correctly!! :)
Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top