Sick time accured and used

[Jamie alexander]

I would like to create a spread for sick accrue and used. The employees accrue 8 hours each month. I have attached what I have but missing a step.

 

[Jamie alexander]

sick time.xlsx
	A	B	C	D	E	F	G
1	Month	Accrured	Used	Balance	Award Time?	Sick time per month	8
2	January	0		0		put yes in E for when time is awarded
3	February	0		0
4	March	0		0
5	April	0		0
6	May	0		0
7	June	0		0
8	July	0		0
9	August	0		0
10	September	0		0
11	October	0		0
12	November	0		0
13	December	0		0
14	Total	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF([@[Award Time?]]="Yes",G$1,0)
D2	D2	=B2-C2
D3:D13	D3	=B3-C3+D2
B14	B14	=SUBTOTAL(109,[Accrured])
C14	C14	=SUBTOTAL(109,[Used])
D14	D14	=SUBTOTAL(109,[Balance])

Cells with Data Validation
Cell	Allow	Criteria
E2:E13	List	Yes

When I use the =IF([@[Award Time?]]="Yes",G$1,0) I get an error that said name is incorrect

 

[Candyman8019]

That's not the correct syntax for named ranges. It would be simply AwardTime if you have created that named range. Essentially, you want to say:
=IF(E2="Yes",G$1,0) and copy that formula down the column.

 

[Jamie alexander]

That's not the correct syntax for named ranges. It would be simply AwardTime if you have created that named range. Essentially, you want to say:
=IF(E2="Yes",G$1,0) and copy that formula down the column.

Thanks Got this part. now have trouble with subtotal.

 

[Candyman8019]

assuming you have named your range "accrured" then simply remove the [ ] from your formula.
Alternatively, you could use =subtotal(109,B2:B13)

 

[Jamie alexander]

Thank you so much for your help.

 

[Candyman8019]

Any time

 

[ExceLoki]

That looks much more functional. The only thing is the total on D14 won't make sense since that column is a running total.
Also, the way you have it set up, the Award Time column requires the word "yes" to make it accrue 8hrs that month.

getting rid of the totals is simple, i added the award time column in case of a LOA or some other reason that a person might not be awarded the 8 hours for that month

So My calendar year is July to June and each month they get 8 of sick time. So the award time column I would put 8?

put yes in the award column if they get the hours