0

I have a column ExpireDate and am trying to return an end of the month date based on the created date is less or equal than the 10th of the month will return the last day of the month, if is greater than the 10th of the month will return the last day of the following month.

e.g. Created is 05/02/21 would return ExpireDate 28/02/21, however if created is 11/02/21 (or onwards) would return ExpireDate 31/03/21

With this in mind I have 3 formula that are working but I would like to combine them to have only one column.

The first formula will return the 10th of the month:

=DATE(YEAR(Created),MONTH(Created),10)

The second formula will return the last day of the current month:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

The third formula returns the last day of the month based on todays date

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1

I tried the below to combine the formulas but have hit a brick wall

IF[Created]=>DATE(YEAR(Created),MONTH(Created),10), DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1, =DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1

Any Ideas?

Improve this question

1 Answer 1

Reset to default
0

Try using this:

=IF(DAY(Created)<11,DATE(YEAR(Created),MONTH(Created)+1,0),DATE(YEAR(Created),MONTH(Created)+2,0))

Return data type of calculated field as Date and Time (You can find this option below formula textbox)

Improve this answer
2
  • Make sure to set your calculated field value to be a Time and Date data type.
    – Matt
    Feb 5, 2021 at 20:12
  • Thanks, That solved my issue!!
    – mtagliaferri
    Feb 6, 2021 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.