We help IT Professionals succeed at work.

How to calculate days worked from start date

Gary Chalmers
on
I am trying to write a query to work out the number of days my employees have worked since they started at the company and for the report have a column named DaysEmployed.

Any help would be greatly appreciated.
Comment
Watch Question

GrahamSkanRetired
Top Expert 2012

Commented:
Do you want to work from a rule, such as counting all the days, subtracting weekends and bank holidays, or do you have attendance records in your database?

Author

Commented:
Hi Graham. No requirement to subtract weekends and bank holidays. it is literally just to have a list of the employees and number of days the have been with the company
Consulting
Distinguished Expert 2017
Commented:
Hi,

Assuming you have a table named Employee, with at least 3 fields: EmployeeName, Start, End.
you can write query as follow:
SELECT	Employee.EmployeeName,
		DateDiff("d", [start], iif(isNull([end]), now, [End])) AS DaysEmployed
FROM	Employee;

Open in new window

Adjust as needed for your database;
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29071143.xlsx

Author

Commented:
Thanks Fabrice