Forum Discussion

Helper I

3 years ago

Solved

Create custom date column based on another date

Hi,

I want to create a custom date column with displays 15th of every month and the end of the month based on a date column.

Any day less than or equal to 15th should display 15th and any day less than or equal to the End of the month should display end of month.

For example-->

Given Date Output Date

10/9/2022 10/15/2022

10/11/2022 10/15/2022

10/17/2022 10/31/2022

10/20/2022 10/31/2022

11/7/2022 11/15/2022

11/17/2022 11/30/2022

FreemanZ
3 years ago
please try this

Output Date =
IF(
    DAY(Data[Given Date])<=15,
    DATE(YEAE(Data[Given Date]), MONTH(Data[Given Date]),15),
    EOMONTH(Data[Given Date],0)
)

3 Replies

FreemanZ
Super User
3 years ago
Supposing your table named Data, try to add a new column with the code below:
Output Date =
IF(
DAY(Data[Given Date])<=15,
DATE(YEAE(Data[Given Date]), MONTH(Data[Given Date]),15),
ENDOFMONTH(Data[Given Date])
)
- Vatz8
  Helper I
  3 years ago
  Hi by using this formula I get all the dates with 15. But the endofmonth returns the same date as in the given column.
  - FreemanZ
    Super User
    3 years ago
    please try this
    
    Output Date =
    IF(
        DAY(Data[Given Date])<=15,
        DATE(YEAE(Data[Given Date]), MONTH(Data[Given Date]),15),
        EOMONTH(Data[Given Date],0)
    )

Related content