#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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.


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Help with months

i am agree for a job
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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 !
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
3 months prior and 3 months post a date renee Excel Worksheet Functions 2 May 2nd 08 05:46 PM
Todays date plus 3 months & less 3 months Les Stout[_2_] Excel Programming 4 November 1st 06 06:55 PM
Adding months to dates should account for 28-30-31 day months Graham Excel Worksheet Functions 3 February 1st 06 01:06 PM
How do i change 15 months to read 1 year and 3 months? Marty Excel Discussion (Misc queries) 1 February 18th 05 12:21 AM
Dates to months and calculating values for their months jigsaw2 Excel Programming 1 September 5th 03 01:35 PM


All times are GMT +1. The time now is 02:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"