Re: $$Excel-Macros$$ Dates In Excel

2011-10-10 Thread Sam Mathai Chacko 
I was only saying that it would be easier for people to remember 1=Sunday,
2=Monday, 3=Tuesday etc, and that one can rely on the default return_type
(which is 1) without actually having to pass it in the function. Makes the
formula look more comprehensible. :)

Also, in your example below,
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)<2)) will give count of
all Sundays

and

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)>6)) will give count of
all Saturdays, which kind of makes it difficult to correlate to, and more so
with the greater to and less than conditionals used.

So basically, it is easier to remember 1,2,3,4,5,6,7 corresponds to Sunday,
Monday, Tuesday etc

Having said that, this is just my opinion, and I am open to standing
corrected if there is any proven alter-argument.

Regards,

Sam

On Mon, Oct 10, 2011 at 11:55 PM, NOORAIN ANSARI
wrote:

> Dear SAM,
>
> I am agree with your statement..
> and thanks for your valuable advice..
>
> But if we use formula with little bit correction then we can find monday,
> tuesday count.
> it works similar to your formula...
> For Monday
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)<2))
>
> For Tuesday
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&D3)),1)>6))
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
> On Mon, Oct 10, 2011 at 11:39 PM, Sam Mathai Chacko wrote:
>
>> Hey Noorain, what's up :)
>>
>> Similar methods used, but, just to touch-base on the differences, what I
>> made use of is the fact that the default return_type of the WEEKDAY function
>> is 1, and it also means that the first day is expected as a Sunday. So in
>> effect, if you equate the resultant array to any number from 1 to 7, it will
>> be equivalent to Sunday to Saturday respectively. So all you need to do to
>> find the number of Mondays for example from my formula, is to change 1 to 2.
>>
>> In your formula, if you wanted to find the number of Mondays, you'd have
>> to modify the conditional also, from >6(OR =7 cause it can never be greater
>> than 7) to <2(OR =1 cause it can never be less than 1)
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)<2))
>>
>> OR
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)=1))
>>
>> So I would recommend going with
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)) for Monday
>>
>> Regards,
>>
>> Sam
>>
>>
>> On Mon, Oct 10, 2011 at 11:17 PM, Sam Mathai Chacko 
>> wrote:
>>
>>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))
>>>
>>> Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday
>>> etc
>>>
>>> Regards,
>>>
>>> Sam Mathai Chacko (GL)
>>>
>>>  On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane wrote:
>>>
 I am trying to count the number of Sunday's between two date ranges.
 For example, Start Date:  9/25/2011; End Date:  10/24/2011.  The
 number of Sunday's between these two dates are 5.  What's the best way
 to write that in a formula?

 Thanks for your help!

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 <><><><><><><><><><><><><><><><><><><><><><>
 Like our page on facebook , Just follow below link
 http://www.facebook.com/discussexcel

>>>
>>>
>>>
>>> --
>>> Sam Mathai Chacko
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
>
>  --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at

Re: $$Excel-Macros$$ Dates In Excel

2011-10-10 Thread NOORAIN ANSARI 
Dear SAM,

I am agree with your statement..
and thanks for your valuable advice..

But if we use formula with little bit correction then we can find monday,
tuesday count.
it works similar to your formula...
For Monday
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),1)<2))

For Tuesday

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&D3)),1)>6))

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Mon, Oct 10, 2011 at 11:39 PM, Sam Mathai Chacko wrote:

> Hey Noorain, what's up :)
>
> Similar methods used, but, just to touch-base on the differences, what I
> made use of is the fact that the default return_type of the WEEKDAY function
> is 1, and it also means that the first day is expected as a Sunday. So in
> effect, if you equate the resultant array to any number from 1 to 7, it will
> be equivalent to Sunday to Saturday respectively. So all you need to do to
> find the number of Mondays for example from my formula, is to change 1 to 2.
>
> In your formula, if you wanted to find the number of Mondays, you'd have to
> modify the conditional also, from >6(OR =7 cause it can never be greater
> than 7) to <2(OR =1 cause it can never be less than 1)
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)<2))
>
> OR
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)=1))
>
> So I would recommend going with
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)) for Monday
>
> Regards,
>
> Sam
>
>
> On Mon, Oct 10, 2011 at 11:17 PM, Sam Mathai Chacko wrote:
>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))
>>
>> Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday
>> etc
>>
>> Regards,
>>
>> Sam Mathai Chacko (GL)
>>
>>  On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane wrote:
>>
>>> I am trying to count the number of Sunday's between two date ranges.
>>> For example, Start Date:  9/25/2011; End Date:  10/24/2011.  The
>>> number of Sunday's between these two dates are 5.  What's the best way
>>> to write that in a formula?
>>>
>>> Thanks for your help!
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>
>
>
> --
> Sam Mathai Chacko
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Dates In Excel

2011-10-10 Thread Sam Mathai Chacko 
Hey Noorain, what's up :)

Similar methods used, but, just to touch-base on the differences, what I
made use of is the fact that the default return_type of the WEEKDAY function
is 1, and it also means that the first day is expected as a Sunday. So in
effect, if you equate the resultant array to any number from 1 to 7, it will
be equivalent to Sunday to Saturday respectively. So all you need to do to
find the number of Mondays for example from my formula, is to change 1 to 2.

In your formula, if you wanted to find the number of Mondays, you'd have to
modify the conditional also, from >6(OR =7 cause it can never be greater
than 7) to <2(OR =1 cause it can never be less than 1)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)<2))

OR

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A16&":"&A17)),2)=1))

So I would recommend going with
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=2)) for Monday

Regards,

Sam

On Mon, Oct 10, 2011 at 11:17 PM, Sam Mathai Chacko wrote:

> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))
>
> Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday
> etc
>
> Regards,
>
> Sam Mathai Chacko (GL)
>
> On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane wrote:
>
>> I am trying to count the number of Sunday's between two date ranges.
>> For example, Start Date:  9/25/2011; End Date:  10/24/2011.  The
>> number of Sunday's between these two dates are 5.  What's the best way
>> to write that in a formula?
>>
>> Thanks for your help!
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Sam Mathai Chacko
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Dates In Excel

2011-10-10 Thread Sam Mathai Chacko 
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

Where A1 and B1 are your start dates, and 1=Sunday, 2=Monday, 3=Tuesday etc

Regards,

Sam Mathai Chacko (GL)

On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane  wrote:

> I am trying to count the number of Sunday's between two date ranges.
> For example, Start Date:  9/25/2011; End Date:  10/24/2011.  The
> number of Sunday's between these two dates are 5.  What's the best way
> to write that in a formula?
>
> Thanks for your help!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
Sam Mathai Chacko

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Dates In Excel

2011-10-10 Thread NOORAIN ANSARI 
Dear Rocky,

Please try it..and see attached sheet..

*=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&D3)),2)>6))

*--
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Mon, Oct 10, 2011 at 10:57 PM, RockyFontane  wrote:

> I am trying to count the number of Sunday's between two date ranges.
> For example, Start Date:  9/25/2011; End Date:  10/24/2011.  The
> number of Sunday's between these two dates are 5.  What's the best way
> to write that in a formula?
>
> Thanks for your help!
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel


Count only Sunday.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Dates in Excel

2011-05-27 Thread hanumant shinde 
Just replace the year from 2011 to 2010. by using replace all



- Original Message 
> From: Rich Prince 
> To: MS EXCEL AND VBA MACROS 
> Sent: Thu, 26 May, 2011 6:49:10 PM
> Subject: $$Excel-Macros$$ Dates in Excel
> 
> I have a column of various dates that have the incorrect year, for
> example:
> 
> 9/1/2011
> 12/17/2011
> 10/31/2011
> etc, etc...
> 
> The year should be 2010 not 2011. Is there a quick remedy to correct
> this problem for several hundred dates?  The Month and day are correct
> - just not the year.
> 
> Any ideas?
> 
> -- 
>--
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links : 
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> 
> To post to this group, send email to excel-macros@googlegroups.com
> 
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
> 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Dates in Excel

2011-05-26 Thread Shreedar Pandurangaiah 
Hi,

You can select all the dates and use Control + H
Find What: 2011
Replace With: 2010
then click on Replace All button. It will change all the 2011 to 2010

Hope this helps!

Thanks,
Shreedar

On Thu, May 26, 2011 at 11:19 PM, Rich Prince  wrote:

> I have a column of various dates that have the incorrect year, for
> example:
>
> 9/1/2011
> 12/17/2011
> 10/31/2011
> etc, etc...
>
> The year should be 2010 not 2011. Is there a quick remedy to correct
> this problem for several hundred dates?  The Month and day are correct
> - just not the year.
>
> Any ideas?
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Dates in Excel

2011-05-26 Thread STDEV(i) 
Assuming that you r date is in Cell A1
Convert them  with this formula:

Formula in B1=Date(2010,Month(A1),Day(A1))



On Fri, May 27, 2011 at 12:49 AM, Rich Prince  wrote:

> I have a column of various dates that have the incorrect year, for
> example:
>
> 9/1/2011
> 12/17/2011
> 10/31/2011
> etc, etc...
>
> The year should be 2010 not 2011. Is there a quick remedy to correct
> this problem for several hundred dates?  The Month and day are correct
> - just not the year.
>
> Any ideas?
>
>

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel