test_man2
New Member
- Joined
- Aug 28, 2007
- Messages
- 35
- Office Version
- 365
- Platform
- Windows
Greetings!
I have a yearly calendar that I keep track of my time off. I have it set up to where when I put a "T" above a date, eight hours is automatically subtracted from my available balance of time off (or "T4 and four hours are subtracted). The issue is my available amount of time off increases every 14 days and the balance that the company shows that I have will not match what my spreadsheet has because I reserve time off throughout the whole year. What formula can I put into a cell that will give me this answer: Beginning balance - balance used + balance earned every 14 days. For example:
Beginning balance: 300 hours (final balance taken from the previous year's calendar and manually entered)
Balance used (5 days in one month, 3 days in another, 1 day in another): 72
Balance earned every 14 days: 15 hours (this can be different based upon the number of years you've been with the company, so I would reference a cell in the formula)
I tried using the formula given in the "Add 2.57 Hours Every Sunday" thread, but it's not working quite right.
I have the rest of the worksheet set up where one cell reads the available number of days (E6), another cell shows the total number of days taken (E7) based upon what has been entered onto the calendar, and a third cell (I6) that shows the number of days available by subtracting the number from E7. Cell E6 reads the number of hours available in cell R68 and divides it by eight. So it looks like the above-requested formula's results needs to show up in R68. My calendar covers cells C8 to Y66 with each month being seven columns x 14 rows (first row is the name of the month, next row is the days of the week, and then every other row after that is the date with the cell above it reserved for the time-off indicator (T or T4)). It is a perpetual calendar in that I just need to put the year in cell U3 and the dates are automagically updated. I wish I could say that I was the creator of this, but I'm not! I just tweaked it for my use.
If you need more information, please feel free to ask!
I have a yearly calendar that I keep track of my time off. I have it set up to where when I put a "T" above a date, eight hours is automatically subtracted from my available balance of time off (or "T4 and four hours are subtracted). The issue is my available amount of time off increases every 14 days and the balance that the company shows that I have will not match what my spreadsheet has because I reserve time off throughout the whole year. What formula can I put into a cell that will give me this answer: Beginning balance - balance used + balance earned every 14 days. For example:
Beginning balance: 300 hours (final balance taken from the previous year's calendar and manually entered)
Balance used (5 days in one month, 3 days in another, 1 day in another): 72
Balance earned every 14 days: 15 hours (this can be different based upon the number of years you've been with the company, so I would reference a cell in the formula)
I tried using the formula given in the "Add 2.57 Hours Every Sunday" thread, but it's not working quite right.
I have the rest of the worksheet set up where one cell reads the available number of days (E6), another cell shows the total number of days taken (E7) based upon what has been entered onto the calendar, and a third cell (I6) that shows the number of days available by subtracting the number from E7. Cell E6 reads the number of hours available in cell R68 and divides it by eight. So it looks like the above-requested formula's results needs to show up in R68. My calendar covers cells C8 to Y66 with each month being seven columns x 14 rows (first row is the name of the month, next row is the days of the week, and then every other row after that is the date with the cell above it reserved for the time-off indicator (T or T4)). It is a perpetual calendar in that I just need to put the year in cell U3 and the dates are automagically updated. I wish I could say that I was the creator of this, but I'm not! I just tweaked it for my use.
If you need more information, please feel free to ask!
