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 and was awarded 16 hours at the end and it automatically updates the to year? I also want to be able to add a cell in between and the formulas will update.
I have attached what it looks like. Along the lines of new emlpoyees the formula got deleted and I cannot get it back.
Any ideas?
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.
What you have posted is a summary sheet. Where is the data table? In order to answer your question you need to post some non-sensitive sample data. The sheet you have posted calculates off of some kind of data source. Without that it's hard to give you much direction.
How long ago did you lose the formula?
Have you tried:
Right Clicking the File > Properties >Previous Versions
Are there any files that you can restore to get the formula back?
