Need help with formula on this spreadsheet

ladyinblack1964

New Member
Joined
Aug 6, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hello, thank you for adding me. I am not very good with Excel so I need a hand with a spreadsheet, which is attached.
Someone else created this for me years ago. Basically, it added in the hours of leave I earned each month, and allowed me to subtract the hours I used. I was earning 11.67 hours per month.
Everything was going along okay until my employer changed all the paid time off rules.
I have put the amount of time I already have in my PTO bank in the top line. I've also put 11.67 hours that I am earning currently.

As of September 1, 2021, I will be earning 13.42 hours biweekly.
After my 10th anniversary with my employer on August 17 of next year, I will be earning 16.34 hours biweekly.

Leave accrual begins on September 1, and must be used in its entirety by August 31 of the following year.

I need someone to create a formula that reflects what I will now be earning biweekly. The original formula was a "=D28+B29-C29" kind. But I don't know how to fix it so it reflects biweekly deposits to my PTO bank.

I hope this all makes sense. I think it is a pretty easy formula for folks who already know how to use the program well! Many thanks!

New PTO Balance 2021-2022.xlsx
ABCD
1MonthTime EarnedTime Used Balance
2end of July 202142.01
3August11.67
4September
5October
6November
7December
8Jan-22
9February
10March
11April
12May
13June
14July
15August
16September
17October
18November
19December
20Jan-23
Sheet1
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,338
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If necessary, edit the start date.

T202108a.xlsm
ABCD
1MonthTime EarnedTime Used Balance
231-Jul-2142.01
331-Aug-2111.6753.68
414-Sep-2113.4267.10
528-Sep-2113.4280.52
612-Oct-2113.4293.94
726-Oct-2113.42107.36
89-Nov-2113.42120.78
923-Nov-2113.42134.20
107-Dec-2113.42147.62
1121-Dec-2113.42161.04
124-Jan-2213.42174.46
1318-Jan-2213.42187.88
141-Feb-2213.42201.30
1515-Feb-2213.42214.72
161-Mar-2213.42228.14
1715-Mar-2213.42241.56
1829-Mar-2213.42254.98
1912-Apr-2213.42268.40
2026-Apr-2213.42281.82
2110-May-2213.42295.24
2224-May-2213.42308.66
237-Jun-2213.42322.08
2421-Jun-2213.42335.50
255-Jul-2213.42348.92
2619-Jul-2213.42362.34
272-Aug-2213.42375.76
2816-Aug-2213.42389.18
2930-Aug-2216.34405.52
1e
Cell Formulas
RangeFormula
A4:A29A4=A3+14
D3:D29D3=D2+B3-C3
 
Solution

ladyinblack1964

New Member
Joined
Aug 6, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
If necessary, edit the start date.

T202108a.xlsm
ABCD
1MonthTime EarnedTime Used Balance
231-Jul-2142.01
331-Aug-2111.6753.68
414-Sep-2113.4267.10
528-Sep-2113.4280.52
612-Oct-2113.4293.94
726-Oct-2113.42107.36
89-Nov-2113.42120.78
923-Nov-2113.42134.20
107-Dec-2113.42147.62
1121-Dec-2113.42161.04
124-Jan-2213.42174.46
1318-Jan-2213.42187.88
141-Feb-2213.42201.30
1515-Feb-2213.42214.72
161-Mar-2213.42228.14
1715-Mar-2213.42241.56
1829-Mar-2213.42254.98
1912-Apr-2213.42268.40
2026-Apr-2213.42281.82
2110-May-2213.42295.24
2224-May-2213.42308.66
237-Jun-2213.42322.08
2421-Jun-2213.42335.50
255-Jul-2213.42348.92
2619-Jul-2213.42362.34
272-Aug-2213.42375.76
2816-Aug-2213.42389.18
2930-Aug-2216.34405.52
1e
Cell Formulas
RangeFormula
A4:A29A4=A3+14
D3:D29D3=D2+B3-C3


Thank you very much! I think this should do it.
Only (and I hope this does not make me sound stupid) I don't understand how to get the information from the mini sheet to my own spreadsheet. I also am not sure how to or where to enter the formulae. I tried to do it and I got ##value##. I confess I know little or nothing about Excel.

Is there a way to download the mini sheet so I can just use that?

Thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,515
Office Version
  1. 365
Platform
  1. Windows
Is there a way to download the mini sheet so I can just use that?
Open a blank worksheet.
Click this button on Dave's mini-sheet
1628397889774.png


Select cell A1 in your blank worksheet.
Paste
 

ladyinblack1964

New Member
Joined
Aug 6, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Thank you very much! This is a huge help to me. :)
 

Forum statistics

Threads
1,175,733
Messages
5,899,159
Members
434,750
Latest member
XLPandit

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