Calculate number of leave days per employee per month

kristinacamua

Hello.

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.

Regards,

Kristina

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)