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 MI     www.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 !

Thanks !

Lionel

here's the spreadsheet : https://docs.google.com/spreadsheets/d/19xp-UbP0EY5FHLJjdxgdtoLWyrYMIrfa4L2ULwsldMM/edit?usp=sharing

---

Hi Yogi !

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 !

The spreadsheet is public and the link is there : https://docs.google.com/spreadsheet/ccc?key=0Algvi-HXKyzmdE05SzRfOU9SWEhiczg5cTYtcEItNEE&usp=sharing

Sheet objectifs is the problem.

Aspirin is provided...

Thanks !

Lionel COSTE

-----------------------------------------------------------------------------------------------------------------------------------------------------------------