Forum Discussion
Automatic formulas
I want to creat a tracker for PTO (Paid Time Off) Each employee get 24 a year (16 hours a month/24 days a year) I want to be able to input Jane Doe took 2 days (16 hours ) at begining of June a...
Hi Monica,
Yes, to advise with formulas we need to know more exactly how your PTO algorithm works. For example, current year means today's year or year on the date of record; how received hrs shall affect the summary (do you calculate balance of hrs), etc.
As for formulas - better to use Excel tables to work with records, it'll be much more flexible, at least from adding new records point of view. In your file summarise used hrs by year could be done as
=SUMPRODUCT((YEAR($B$6:$B$14)=YEAR($B6))*$D$6:$D$14)
Adding the record you need to copy cell with formula into new row and possible expand the range of the calculations (if new record is at the end).
With table the formula looks like
=SUMPRODUCT((YEAR([Date])=YEAR([@Date]))*[Used hrs])
and it will be automatically propagated on new records.
Both variants are in attached file.