Forum Home
Press F1
Thread ID: 130042	2013-03-24 19:20:00	Help with creating an Excel formula - maybe a combination of COUNT and DATE reference	Rog (17042)	Press F1

Post ID	Timestamp	Content	User
1333910	2013-03-24 19:20:00	I have a spreadsheet with data about jobs completed on different dates by different people. There is a column with the names of the people who completed the job and a column with the date on which the job was completed. Each person will typically do 3 or 4 jobs on any one day so their name will potentially appear several times in a week or a months worth of data. What I am trying to count is the number of "Equivalent person days worked" i.e. if the Name column contains 3 names (Tom, Dick and Harry) and the dates span a working week (5 days) If Tom did jobs on all 5 days, Dick did jobs on all 5 days and Harry did jobs on 3 out of 5 days - the equivalent person days worked would be 13 days. However, my "name" column contains about 20 different names and the data set is for a month so some of the names repeat approx 60 times (3 jobs on each of 20 seperate days) whereas others appear less frequently due to not having worked on some days or having done les jobs on some individual days. I have tried COUNTIFS and NETWORKDAYS and varoius combinations of other things but am struggling to work it out - not even sure if it is possible.......can anyone help?	Rog (17042)
1333911	2013-03-24 20:03:00	try vlookup or hlookup those two spring quickly to mind	beama (111)
1333912	2013-03-25 00:19:00	You are describing two "multi-to-multi" joins, which require at least 6 tables. Excel is missing several key features that any database product will contain. I'd suggest trailing time-management software. Otherwise, you going to need some kind of database software, at the very least SQL lite (free) or Microsoft Access (part of many Office suites)	kingdragonfly (309)
1