Perpetual balance update

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. 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!
 

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Again, I really appreciate the time and effort you put into this! You've given me some great formulas to work with! Unfortunately, I do see that some of them won't be able to be used year over year because the beginning of the accrual period won't be 1/1 every year and the end of the accrual period won't be 12/31. It will instead depend on what the number of days are left in the accrual period when the current year ends. So the accrual period next year might start on 1/5 or 1/10 or some other date in January.
Please don't think I'm not grateful! I am very grateful for what you've given me!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
It’s been quite a while since I have posted here, but I haven’t given up on finding a solution to this perpetual calendar! And I want to reiterate that I appreciate all that Dossfm0q has posted already! Perhaps this post will help! Hopefully, this post isn’t too long!

Example spreadsheet that this post refers to is in the next post:

First, the request: I’d like a formula that will add all the time I took off during the time period mentioned in columns E and F and place it in the cell in column I that corresponds to that timeframe. This will then show the actual amount of time off I have left in the year.

Second, the flaw in the current spreadsheet: the total number of hours being taken for the year is being subtracted from the available number of hours currently and doesn’t account for any more hours being accrued. For example, over the course of this year, I have allocated 11 days to take off. The current calendar would show that I have taken 88 hours off and had less than 75 hours of time-off left. Instead, it needs to subtract only the number of hours that I have already taken and not future ones as well.

I showed my boss my calendar and he noticed that flaw in it and came up with the idea seen in columns D – J. To make this a calendar able to be used annually, I added the input fields in column B and made formulas in columns E – G in order for them to change based upon those input fields. My boss’s idea was to simply put the number of hours taken off in that particular time period in column I (see examples in cells I13, 15, 20, 25, and 27). What I’d like to do instead is to use a calendar on another worksheet (similar to columns L – Z, including the layout; see Post #4 for the layout) to automatically enter the number of hours taken when a “T” is placed above that day. You can see that demonstrated by looking at the formula in cells I2 and I3. To see that demonstrated, delete the “T” in cell N9 and you’ll see the amount in I3 change. That formula works great as long as the dates are in the same month, but it won’t work from cell I4 and the rest of the column because the timeframe carries over to a new month, which results in columns that haven’t been used before, and rows that have been called before.

Points to remember – not in the example below, but the calendar in Post #4 will change based upon what year is entered; so when 2022 is entered on the calendar, January will start on Saturday and end on Monday, and February will start on Tuesday and end on Monday. So whatever formula is created has to have variables in it to accommodate the ever-changing dates.

-- the variables in cells b1, 2, 4, and 6 will most likely change each year. Next year’s first pay day will probably not be 1/13, and the beginning time-off balance might not be 150. That depends on the ending balance of the previous year. And b6 might changed based upon the whims of the company.

-- (this isn’t related to the requested formula) column J has a conditional formatting on it that if the total goes above 400, the cell turns red.

I’m not at all familiar with array, TRANSPOSE, INDIRECT, and have never worked with MOD, so if any of those functions are used in the solution, please explain them or direct me to a “Excel for Dummies”-level explanation!
 

test_man2

New Member
Joined
Aug 28, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Cell Formulas
RangeFormula
E2:E27E2=F2-13
F2:F27F2=G2-7
G2G2=B2
H2H2=B6
I2I2=COUNTIF(L3:R5,"T")*8
G3:G27G3=G2+14
H3:H27H3=$H$2+H2
I3I3=COUNTIF(L5:O9,"T")*8
J2:J27J2=$B$4+H2-I2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J2:J27Cell Value>400textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top