Help with months
 

Hi !

I have data that is organized like this

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

So what I'd like to know is how many days each grid has. For eg:grid 1 has been sampled form 11/10/12 to 15/10/12 in Oct, that's 5 days and similarly in January 13 it has 5 days. grid 2 worked in dec12 for 4 days from 4th to 7th.

So I would like my output to look like this

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

Please help !
Thanks in advance!

Help with months
 

Hi Nitya,

Am Mon, 10 Aug 2015 06:15:46 -0700 (PDT) schrieb Nitya Satheesh:

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Grid"
There is a suggestion with formula and one with Pivot.
I would prefer Pivot.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help with months
 

On Monday, August 10, 2015 at 10:39:01 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 06:15:46 -0700 (PDT) schrieb Nitya Satheesh:

Grid Date
1 11/10/12
1 11/10/12
1 15/10/12
1 1/1/13
1 5/1/13
1 10/1/13
2 4/12/12
2 5/12/12
2 7/12/12
2 10/4/13
3 1/1/12
3 2/1/12
3 3/1/12
3 4/1/12
4 10/6/11
4 11/6/11
4 15/6/11
4 17/6/11
5 19/6/11
5 20/5/11
5 22/5/11

grid Jan11 Feb11 MAr11 ....Oct12...dec12
1 5 0 0 5 0
2 0 0 0 0 4

please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Grid"
There is a suggestion with formula and one with Pivot.
I would prefer Pivot.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi !
Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

Can this be done?
Please help.
Thanks,
Nitya

Help with months
 

Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help with months
 

On Tuesday, August 11, 2015 at 11:34:03 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,
Thanks a lot Claus! This works perfectly !
I'm so sorry, but if you don't mind , could you please explain the formula,I didn't quite get it.

Help with months
 

Hi Nitya,

Am Tue, 11 Aug 2015 01:37:17 -0700 (PDT) schrieb Nitya Satheesh:

On Tuesday, August 11, 2015 at 11:34:03 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I'm so sorry, but if you don't mind , could you please explain the formula,I didn't quite get it.

In the E1:Z1 I have dates with always the first of month.In E2:E6 there
a the unique values of the grid.
In F2 I check if there is a Max if the grid in column A is the grid of
E2 and the month and the year in column B is month and year of F1.
If so I substract the Max with this conditions and the Min with this
conditions and add 1 for the start day because the difference is one day
less than the count of days.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help with months
 

On Tuesday, August 11, 2015 at 3:46:28 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Tue, 11 Aug 2015 01:37:17 -0700 (PDT) schrieb Nitya Satheesh:

On Tuesday, August 11, 2015 at 11:34:03 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 10 Aug 2015 22:07:49 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Claus, but what I needed is,(and I don't think I explained that properly)is for it to show me the total number of days a grid has been sampled. so in my data if you look at grid 4 it worked from 10th to 17th so I would like the output to show me 8 days for jun-11 similarly for the other grid 1 it worked for 5 days in october-12(11th to 15th) and 10 days in jan-13(1st-10th)

now I got it ;-)
Please look again in OneDrive

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I'm so sorry, but if you don't mind , could you please explain the formula,I didn't quite get it.

In the E1:Z1 I have dates with always the first of month.In E2:E6 there
a the unique values of the grid.
In F2 I check if there is a Max if the grid in column A is the grid of
E2 and the month and the year in column B is month and year of F1.
If so I substract the Max with this conditions and the Min with this
conditions and add 1 for the start day because the difference is one day
less than the count of days.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,
Thanks for the explanation. I have one question - is it necessary to check if there is a max in f2? Can I directly check if the month and year of column b is the same as f1 then subtract the max and min with the same conditions you have provided? what is the reason for putting the max function?

Thanks again for the explanation !

Nitya

Help with months
 

Hi Nitya,

Am Tue, 11 Aug 2015 04:00:51 -0700 (PDT) schrieb Nitya Satheesh:

Thanks for the explanation. I have one question - is it necessary to check if there is a max in f2? Can I directly check if the month and year of column b is the same as f1 then subtract the max and min with the same conditions you have provided? what is the reason for putting the max function?

if you e.g. have 10.06.2015 until 17.06.2015 and you substract Max with
Min you get 7 but there are 8 days. So you have to add 1. If you don't
check for Max and directly calculate Max-Min+1 you get the wrong result
of 1 if there are no days with the conditions.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help with months
 

i am agree for a job

Help with months
 

On Tuesday, August 11, 2015 at 4:44:59 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Tue, 11 Aug 2015 04:00:51 -0700 (PDT) schrieb Nitya Satheesh:

Thanks for the explanation. I have one question - is it necessary to check if there is a max in f2? Can I directly check if the month and year of column b is the same as f1 then subtract the max and min with the same conditions you have provided? what is the reason for putting the max function?

if you e.g. have 10.06.2015 until 17.06.2015 and you substract Max with
Min you get 7 but there are 8 days. So you have to add 1. If you don't
check for Max and directly calculate Max-Min+1 you get the wrong result
of 1 if there are no days with the conditions.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks a lot Claus!!! This helped me a lot !