Im looking for a formula for the first of the month after 30 working days

BriannaRocamora

In relation to eligibility i want to be able to put in date of hire and have it produce when they will be eligible

heyjay

=MONTH(Date@row + 30) this wil just give you the month in numbers (1-12 for Jan - Dec). You'll want to add IF statement if you want the name of the month.

=IF(MONTH(Date@row + 30) = 1, "January", 
IF(MONTH(Date@row + 30) = 2, "February", 
IF(MONTH(Date@row + 30) = 3, "March", 
IF(MONTH(Date@row + 30) = 4, "April", 
IF(MONTH(Date@row + 30) = 5, "May", 
IF(MONTH(Date@row + 30) = 6, "June", 
IF(MONTH(Date@row + 30) = 7, "July", 
IF(MONTH(Date@row + 30) = 8, "August", 
IF(MONTH(Date@row + 30) = 9, "September", 
IF(MONTH(Date@row + 30) = 10, "October", 
IF(MONTH(Date@row + 30) = 11, "November", 
IF(MONTH(Date@row + 30) = 12, "December"
))))))))))))

BriannaRocamora

This is giving me a "date expected" if someone is hired 7/10 then they wont be eligible for benefits until the first of the next month after 30 days so i need a formula that will produce me 9/1

Nic Larsen

Does this work for what you're trying to do:

=IF(Date@row < TODAY(30), DATE(YEAR(Date@row), MONTH(Date@row) + 2))

Sarah Keortge

Try this:

=IF(DAY([hire date]@row + 30) = 1, [hire date]@row + 30, IF(AND(DAY([hire date]@row + 30) <> 1, MONTH([hire date]@row + 30) = 12), DATE(YEAR([hire date]@row) + 1, 1, 1), DATE(YEAR([hire date]@row + 30), MONTH([hire date]@row + 30) + 1, 1)))

You first check to see if 30 days from the hire date is the first of a month because you don't want to add an additional month on to those.

Next you check to see if 30 days from the hire date will land in December because then you need build your date with the new year.

Finally, you are left with finding 30 days from the hire date and building a date that reflects the next month.