1

I'm trying to create a couple of fields to calculate the eligibility date for employee benefits.

-The first one should return a date of either the 1st or the 15th of the month 60 days after Hire Date. It would need to determine which date is soonest after the 60 days. For example, a hire date of 12/7/2022 would return the result 2/15/2023 while a hire date of 12/15/2022 would return the result of 3/1/2022

-The second one would be the same concept except the return dates would be either the 15th or the last day of the month.

Is this even something that can be done with a formula field?

*Edited to add:

I have a temporary formula in as a placeholder while I build everything out. It's set to return the 1st of the month after 60 days and is working as expected. I just found out that we needed the 15th as well and I don't know where to go from here.

DATE(IF(MONTH(Employee_ID__r.Date_of_Hire__c +60) = 12,
YEAR(Employee_ID__r.Date_of_Hire__c +60)+1,
YEAR(Employee_ID__r.Date_of_Hire__c +60)),
IF(MONTH(Employee_ID__r.Date_of_Hire__c +60)=12, 1,
MONTH(Employee_ID__r.Date_of_Hire__c +60)+1), 1)
Improve this question
5
  • 1
    welcome to SFSE! Please see How to Ask on getting the most from the Community -- yes, it can be done -- what have you tried?
    – cropredy
    Dec 7, 2022 at 17:42
  • @cropredy I have a temporary formula in as a placeholder while I build everything out. It's set to return the 1st of the month after 60 days and is working as expected. I just found out that we needed the 15th as well and I don't know where to go from here. DATE(IF(MONTH(Employee_ID__r.Date_of_Hire__c +60) = 12, YEAR(Employee_ID__r.Date_of_Hire__c +60)+1, YEAR(Employee_ID__r.Date_of_Hire__c +60)), IF(MONTH(Employee_ID__r.Date_of_Hire__c +60)=12, 1, MONTH(Employee_ID__r.Date_of_Hire__c +60)+1), 1)
    – Jaymee
    Dec 7, 2022 at 17:50
  • 2
    Welcome to Salesforce Stack Exchange (SFSE)! Please do not post additional information that is for your question, or that is an answer to a question someone posted in a comment, as a comment. It is easy for this info to get lost, as many people do not read all of the comments. It's better to have all this information in the one post. Please edit your original question to add the additional information. At the bottom left of every post you see "Share Edit Follow Flag". Click Edit under your question to add the information.
    – Moonpie
    Dec 7, 2022 at 18:18
  • 1
    @Moonpie Apologies, I have edited my original post to include the additional information.
    – Jaymee
    Dec 8, 2022 at 3:27
  • This question seems eerily similar to this recent question: salesforce.stackexchange.com/q/391524/81648....
    – Moonpie
    Dec 8, 2022 at 3:37

1 Answer 1

Reset to default
0

Your formula for the first use case would be something like this:

IF (DAY(Employee_ID__r.Date_of_Hire__c) < 15,
    ..formula to calculate 60 days from same month, day 15,
    ..formula to calculate 60 days from same month, day 1)

The reference formulas to use based on the specifics of your use case can be found in this Help article. For your first use case, Variation 3 looks helpful; for your second use case, a combination of Variation 3 and 4

Improve this answer
1
  • 1
    Thank you! I think I was trying to make it a lot more complicated than it needed to be.
    – Jaymee
    Dec 8, 2022 at 3:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .