Can someone please help me find a solution to this?
Please help me rewrite this formula to split the total amount of leave dates requested based on the year the date is in.
For example, count the days within the Leave Date range - how many days in 2023 and how many days in 2024. The total days column will now be 2023 Total Days and another column will be added for 2024 Total Days.
There is currently a formula I am using that counts the total days in the leave date range while subtracting any weekends and 0.5 if a half-day is included in the request.
This is the formula:
if(prop("Half Day Included") == "Yes", ((dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "days") + 1) - 0.5) - ((dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "weeks") * 2) + min(max((day(prop("Leave Date")) % 7 + (dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "days") % 7)) - 5, 0), 2)), 1) * if(prop("Half Day Included") == "No", (dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "days") + 1) - ((dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "weeks") * 2) + min(max((day(prop("Leave Date")) % 7 + (dateBetween(dateEnd(prop("Leave Date")), dateStart(prop("Leave Date")), "days") % 7)) - 5, 0), 2)), 1)
I've created a video explaining it further here: https://www.loom.com/share/934230cf0fdb4066bdbff1a6c2e0a619?sid=0a24b0d1-2832-4b6d-bc73-0ce18fd13271
