PTO available Balance

Robert Wyatt

Board Regular
Joined
Jul 15, 2012
Messages
102
Office Version
  1. 2019
Platform
  1. Windows
I have a f
PTO Register.xlsm
BCDEFGHIJKLMNOPQR
1Employees Date of HireYears of Service Years Months DaysAnnual Accrual DaysMaximun AccrualAccrual Per HoursAccrual Per Pay PeriodAvaiable PTO HoursYears of ServiceAnnual Accrual DaysMaximum AccrualAccrual Per HoursAccrual Per Pay Period
203/22/2024001010800.0384615381.54-010800.0384615381.54
3112960.0461538461.85
4Balance Forward2151200.0576923082.31
5Accrualed Hours-5201600.0769230773.08
6PTO-10252000.0961538463.85
7Sick-
8Vacation-
9Holiday-
10
11Pay Period #Pay DateHoursPTOSickVacationHolidayAccrual RateBalance
122612/31/2023Balance Forward-
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sheet1
Cell Formulas
RangeFormula
C2C2=IF(B2="","",DATEDIF(B2,TODAY(),("Y")))
D2D2=IF(B2="","",DATEDIF(B2,TODAY(),("YM")))
E2E2=IF(B2="","",DATEDIF(B2,TODAY(),("MD")))
F2F2=LOOKUP(C2,N2:O6)
G2G2=LOOKUP(C2,N2:N6,P2:P6)
H2H2=LOOKUP(C2,N2:O6/260)
I2I2=LOOKUP(C2,N2:O6,R2:R6)
J2J2=VLOOKUP(J12+100,J12:J64,1)
Q2:Q6Q2=O2/260
R2:R6R2=P2/52
G5G5=SUM(H13:H38)
G6G6=SUM(E12:E38)
G7G7=SUM(F12:F38)
G8G8=SUM(G12:G38)
G9G9=SUM(H13:H38)
J12J12=SUM(G4)
I13I13=IF(D13="","",LOOKUP($C$2,$N$2:O6/260*D13))
J13J13=IF(D13="","",IF(AND(ISBLANK(B13:H13)),"-",IF(J12+I13-D13-E13-F13-G13-H13<$G$2,J12+I13-D13-E13-F13-G13-H13,)))
ormula in the PTO balance Colum J13 it was working, but for some unknow reason it quit. Could some one look at it and see where I missed up. I for got to add 40 hrs so it would show to #value error.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
you may want to send a message to a moderator/admin (report yourself) asking to delete the erroneous posting. But, you also are not asking a question here. ;) what do you want to have done?
 
Upvote 0
you may want to send a message to a moderator/admin (report yourself) asking to delete the erroneous posting. But, you also are not asking a question here. ;) what do you want to have done?
I'm old sorry I not doing things the way they should be. I'll report them and never post again.
 
Upvote 0
I'm old sorry I not doing things the way they should be. I'll report them and never post again.
xl2bb can be tricky until you get the hang of it. I'm probably older than you.

But, it looks like you did the xl2bb properly here. You just didn't describe your problem.
 
Upvote 0
You just didn't describe your problem.
The OP has, but has pasted the XL2BB in the middle of it
1712008258388.png

1712008291258.png




@Robert Wyatt you are getting the VALUE# error because E13 isn't a blank cell, it has 3 space characters in it.
 
Upvote 0
The OP has, but has pasted the XL2BB in the middle of it
View attachment 109281
View attachment 109282



@Robert Wyatt you are getting the VALUE# error because E13 isn't a blank cell, it has 3 space characters in it.
I don't understand what do you mean by 3 space characters? E13 should be blank unless I have used PTO. J13 should be keeping track of Balance of PTO. If possible, can you tell me how to correct the problem?
 
Upvote 0
E13 isn't blank, you have 3 space characters in it (i.e. the spacebar has been pressed 3 times).
can you tell me how to correct the problem?
Click inside E13 and hit your backspace button 3 times then press enter (or click outside the cell)
 
Last edited:
Upvote 0
E13 isn't blank, you have 3 space characters in it (i.e. the spacebar has been pressed 3 times).

Click inside E13 and hit your backspace button 3 times
I tired what you said and it worked, but before you asked I tried placing 0 in the empty places and it worked as well. It's heck to get old. lol
 
Upvote 0
Solution
I tired what you said and it worked, but before you asked I tried placing 0 in the empty places and it worked as well. It's heck to get old. lol
There are two things you can do to help debug your formulas (i am unsure if you did this or not).
One is to use the formula evaluate tool: Select Formulas from the menu, then in the formula auditing section click on Evaluate Formula. You can click through the dialog box to see how each expression in your formula is evaluated.
The second is to use the F9 function key inside the formula. Highlight an expression (say: A2+B2) and click F9, it will give you the calculation for that expression. (But you need to be careful with this and click ESC key or the newly calculated value will get inserted into your formula).
In some version, just hovering over the highlighted expression will give you the calculation

Here is the Formula Evaluation tool: (I tried to screen shot the F9/Hover version but the screenshot program I have doesn't capture it).

1712015820390.png
 

Attachments

  • 1712015515690.png
    1712015515690.png
    12.8 KB · Views: 25
  • 1712015574687.png
    1712015574687.png
    10.3 KB · Views: 12
  • 1712015771966.png
    1712015771966.png
    35.6 KB · Views: 18
Last edited:
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,126
Members
453,021
Latest member
Justyna P

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
Back
Top