Timesheets with vacations, sick etc

[LancashireLass]

Hi,

I have created a basic timesheet for our employees to make life easier we are going from manual to Excel to be more efficient. However, with the timesheet I have added vacations and each time they have a vacation the main number goes down which is what we want, but...each time an employee sends me their timesheet with the required info for that pay period that's fine, but when they send the next timesheet for the next pay period the numbers revert back to what they were initially. Please see below, they start off with 247.50 but if they take a vacation the "remainder" shows what they have left which is great, but when they start a new timesheet (by deleting the current data) it reverts back and I cannot see right for doing wrong. I am thinking I may need some small table within a workbook for live data but I am not sure how to do this? Any help would be greatly appreciated.

Day of Week	Time In	Time Out		Total Hrs	Regular Hrs	Sick/ Personal Hrs	Other Leave ie bereavement	STAT Hrs	Vacation Hrs
Mon 6-29	9:30 AM	11:00 AM		1.50	1.50
Tue 6-30	3:45 PM	12:00 AM		8.25	8.25
Wed 7-1				0.00	0.00			8.25
Thu 7-2	3:45 PM	12:00 AM		8.25	8.25
Fri 7-3	3:45 PM	12:00 AM		8.25	8.25
Sat 7-4	11:45 PM	8:00 AM		8.25	8.25
Sun 7-5				0.00	0.00
Other				0.00	0.00
				Total Hrs:	34.50	0.00	0.00	8.25	0.00
Mon 7-6	3:45 PM	12:00 AM		8.25	8.25
Tue 7-7	3:45 PM	12:00 AM		8.25	8.25
Wed 7-8	3:45 PM	12:00 AM		8.25	8.25
Thu 7-9	3:45 PM	12:00 AM		8.25	8.25
Fri 7-10				0.00	0.00
Sat 7-11				0.00	0.00
Sun 7-12				0.00	0.00
Other				0.00	0.00
				Total Hrs:	33.00	0.00	0.00	0.00	0.00
	Start	Remainder
VAC Hours	247.5​	247.50​		Biweekly Total Hrs:	67.50	0.00	0.00	8.25	0.00
Sick/Personal Hours	143.00​	143.00​		Rate/Hr:	27.61	27.61	27.61	27.61	27.61

 

[mrshl9898]

You'd need to keep a record of their leave somewhere, functionality wise you could just keep adjusting the figure manually but for record keeping sake best to store it somewhere else with dates and times.

 

[LancashireLass]

So create a table with the info, and this will calculate automatically?

 

[mrshl9898]

create a table, let's see what it looks like, then create a formula from there.

Would also need to know if this rollsover too, and on what dates