Time Off Summary

[zarger11]

In the attached spreadsheet, On the CTO Summary tab, I am calculating the remaning CTO for each person based on the current year. However, I am wondering how I do the same calculation based solely on the current year without removing any of the previous year's attendance history. It is important that we keep the historical data, and still get a view of their CTO balances on in the current year.

Thanks in advance for your help. password is GE2014

Attendance Test.xlsx

[teylyn]

Hello,

on the CTO summary sheet there are a few formulas using Sumifs() (columns B to F). Edit each of these formulas and add a condition that the End date of that leave type must be greater than the Jan-1 of the current year, like

Please Login or Register  to view this content.

The five formulas are

CTO

Please Login or Register  to view this content.

UCTO

Please Login or Register  to view this content.

XCTO

Please Login or Register  to view this content.

FMLA Paid

Please Login or Register  to view this content.

Unpaid

Please Login or Register  to view this content.

cheers, teylyn

[zarger11]

One additional question...

What if I were trying to track time within a specific timeframe?? For example, i needed to know how much FMLA Paid time was used between 4/29/15 and 4/29/16. How would I adjust this formula?:

=SUMIFS(tblLeave[Conversion],tblLeave[Employee Name],[@Associate],tblLeave[Type of Leave],Table5[[#Headers],[FMLA Paid]],tblLeave[End Date],">="&DATE(YEAR(TODAY()),1,1))

Thanks