Calculate number of leave days per employee per month



I am working on two sheets: Employee Entitlements and Leave Register.

Sheet 1: Leave Application, where all the employees submit their PTOs.

Sheet 2: Summary sheet, holds the summary of each employees' leave balance, days used, and leaves incurred per month.

How do I calculate the number of leave days per employee per month? Also how will it work if the leave application spills over to the next month?

Thanks in advance.





  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Assuming it will never overlap into more than one additional month, you will need two helper columns. One for the number of days in the first month and one for the number of days in the second month (when applicable).

    First Month formula would be

    =IF(MONTH([Start Date]@row) = MONTH([End Date]@row), DAY([End Date]@row) - DAY([Start Date]@row), DAY(DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1) - 1) - DAY([Start Date]@row))

    Second Month formula would be:

    =IF(MONTH([End Date]@row) <> MONTH([Start Date]@row), DAY([End Date]@row), 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!