Views:

Product: LABELVIEW & CODESOFT

Version: 2014 & later

 

 

1. First create a Date variable and name it "Month"

 

This can be done by either right clicking date and selecting add or going to the Data Sources drop down selecting date, then add.

2. Create another Date variable and name it "Year"

3. Create a new Formula variable and name this "Leap Year Check"

 Following a similar creation to the date variable, a formula can be created by right clicking formula , selecting add or going up to the Data sources drop down selecting formula, then selecting add. Copy this string and paste it into the new formula "exact(mod(left(year, 4), 4), "0"), exact(right(month, 2), "02"))".

3. Create another Formula variable and name this "Last Day of the Month"

Use the following string to calculate the last day of the month. "if({Leap Year Check}="1", Extract(BestBefore(BestBefore(pad(month(Month),2,0) &"/01/"& Month,"mm/dd/yy",1,"m", 0, "mm/dd/yy", 1033),"mm/dd/yy",-1,"d", 0, "mm/dd/yy", 1033), "/", 2)+1, Extract(BestBefore(BestBefore(pad(month(Month),2,0) &"/01/"& Month,"mm/dd/yy",1,"m", 0, "mm/dd/yy", 1033),"mm/dd/yy",-1,"d", 0, "mm/dd/yy", 1033), "/", 2))"

 

Keywords: How to calculate the last day of the month on a leap year; Last Day of the month; Leap Year