On this project I have been on for much time now, feels like forever, I have begun to carve impressive progress. One of the requirements we needed a quick start and end date. The form contained a form start and end date, and a repeating section where each item had a start and end date. For the most part many entries will be back dated to the first of the month and some stamped for the end of the month. I know with script many things are possible, but I really did not want to add more lines of code for something I believe could be handled with a simple rule.
The first thing I was considering was how to implement this. I did not want it taking away from the form, but I needed it to function. I considered using a button control. I removed the fill color and removed the borders. Once I did this it looked like regular text on the form. Now I can format the text and place it anywhere it was needed.
Setting the value to the first day of the month was really simple. You can use today() with concat and substring, replacing the “Day” with “01″
concat(substring(xdDate:Today(), 1, 4), "-", substring(xdDate:Today(), 6, 2), "-01")
If you notice the first substring, it is actually pulling in the year. When I tried this in the format on the field “mm/dd/yyyy” I received a validation error. Microsoft needs the input in this format “yyyy-mm-dd”, although once the value is written to the date field, it is immediately converted back to the field’s format of “mm/dd/yyyy”. Knowing this will make solving the last day of the month a bit easier, so I thought.
Know that getting the first day of the current month is fairly easy, I figured we could get the first day of the next month and subtract 1 day. This would ensure always getting the last day of the current month. I learned this trick when dealing with Calculated Columns and Date fields in SharePoint. Getting the first day of next month was a little trickier than expected, but eventually figured it out. The initial problem was the date length. The standard date length is 10 characters. When I added 1 to current month if it was a single digit, it returned a single digit. I needed a way to add a leading zero. Good thing I had a need for this same requirement on another project, so was easy to figure out how to add a leading zero and 1 month.
(concat(substring("00", 1, 2 - string-length(substring(xdDate:Today(), 6, 2) + 1))
The first substring tells us the number of digits and how to format it and the nested substring is grabbing the month value, then adding 1 at the end. This brings us very close, but we are still not quite there. Initially I thought I would be able to subtract 1 from the date and I would be done, but this wasn’t the case. So I did a little research and came across a post of similar request and that used the addDays() function and added a negative 1, perfect. This nailed it on the head.
Now putting it all together here is the last day of the current month:
xdDate:AddDays(concat(substring(xdDate:Today(), 1, 4), "-", (concat(substring("00", 1, 2 - string-length(substring(xdDate:Today(), 6, 2) + 1)), (substring(xdDate:Today(), 6, 2)) + 1)), "-01"), -1)
I understand that IP has a calendar picker and this is just as easy to open the calendar to make your selection, but my form could have many items added and using the calendar takes a minimum of 2 clicks. Adding these rules it now takes 1 click. Enjoy!