You may need to extract the data of the last day of each month, such as the last day of the month in which the Payment Time falls, as shown in the following figure.
Note:The example in this document does not involve operations on the hour-minute-second data. To extract the year-month-day data, you can use the LEFT function to extract the first 10 characters of each field.
Use the DATE function in combination with YEAR, MONTH, and other functions to obtain the last day of each month.
Sample data: Contract Payment Facts Table.xlsx
Create an analysis subject and upload the sample data.
Click Formula Column and name the added column as Last Day of Each Month. Enter the formula DATE(YEAR(Payment Time),MONTH(Payment Time)+1,1)-1 and click OK.
Note:You can select the fields you need from the left area or enter the first few letters and select the one you need in the pop-up list. Fields in black entered manually do not take effect.
Formula explanation:
YEAR(Payment Time)
Return the year of the payment time.
MONTH(Payment Time)+1
Return the month after the month of the payment time.
DATE(YEAR(payment time),MONTH(payment time)+1,1)
Return the first day of the month after the month of the payment time.
DATE(YEAR(payment time),MONTH(payment time)+1,1)-1
Return the last day of the month of the payment time.
After you calculate the last day of each month, you can click Save All and Update or perform further calculations.
For the demonstration of this KPI card, see the second figure in section "Preview".
To calculate the last day of the current month, you can use the TODAY function and the DAYSOFMONTH function with the formula DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY())).
YEAR(TODAY())
Return the year of the current date.
MONTH(TODAY())
Return the month of the current date.
DAYSOFMONTH(TODAY())
Return the number of days of the current month.
DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY()))
Return the last day of the current month.
滑鼠選中內容,快速回饋問題
滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy