With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Tuesday, March 11, 2014
yogi_Compute Row By Row For Every Name Number Of Instances of Duration Of Projects In Each Month Of The Year
Google Spreadsheet Post #1557 Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MIwww.energyefficientbuild.com. Mar-10-2014 post by absalon974 (https://productforums.google.com/forum/#!mydiscussions/docs/4t2aHqkqmmA)
Hello, i'm trying to use a QUERY function to aggregate items from a table with startDate and endDate for people subscribing a contract. But the issue I'm in front off is due to my french date format (dd/MM/YYYY), so I cannot use the useful QUERY function nor with the columns i've created, nor with the pivot i'd like to use with both month+year for each entry... So if someone has an idea, i'll take it friendly !
You helped me well last time with the use of the emebedded IF function into a countIF to check the Range before launching the count.
And there's still a small tip i'm not able to do alone... What a frustration ! Grrrr...
So the background of the project is :
Recruiters must have a monthly quota of twelve people who received a contract, but if a contract straddling (covers) many months, it must be counted for each month.
In my example, Memona ASSANI is the manager that signed 2 contracts :
- the first one is begining the 07/01/2014 and ending the 19/04/2014, so he counts for 1 in January, February, March and April
- the second one is begining the 31/03/2014 and ending the 19/06/2014, so he counts for 1 in March, April, May and June
- the global board should indeed show on the Memona ASSANI row : January : 1 February : 1 March : 2 April : 2 May : 1 June : 1
For now it'd just should, it's not... I just obtain zeros...
The concerned sheet is objectifs and the values are provided by the sheet formulaire. After a step of uniting responses (it's a four parts non linear form) the base is the table into the united sheet. As you can see in the united sheet i use a query function wich returns all the recruiters by rows with all startDate and endDate contracts. I don't know if a QUERY function should do the job automaticaly for each month of each year, generating a dynamic table growing each mounth !
I tried also that way, but i'm still a rookie. How google learns humility !