Re: $$Excel-Macros$$ Formula for Month wise calculation

2017-09-30 Thread joseph abi 
Dear Mr. Paul,

It great to formulate such a amazing formula to calculate such difficult
scenario.


Abi Joseph

On Fri, Sep 29, 2017 at 5:48 PM, Paul Schreiner 
wrote:

> This is pretty "convoluted" logic.
> I tried several approaches that soon became extremely complex (as if this
> isn't?)
>
> The approach is this:
> Establish a starting date of 01-Jan of LAST year
> and an Ending Date of -1-Jan of NEXT year.
> (I could've hard-coded these dates, but then you'd have to edit the macro
> in a few months!)
>
> then, I calculate the number of days between those two dates:
> DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
>
> Next, calculate the number of days between this year old starting date and
> the "IN" date.
> $B3-DATE(YEAR(NOW())-1,1,1)
> Now, for the month you're evaluating, find the number of days since the
> first day of the month and this old starting date:
> H$2-DATE(YEAR(NOW())-1,1,1)
>
> I select the MAX() of these two dates and subtract it from the total days.
>
> I do the same thing, calculating the days between the "future" date and
> the "Out" date
> and compare it to the first day of the NEXT month from the reporting month.
> (I have to take into account that the "next" month could also be January
> of the next year, so I use:
> DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31)) to calculate that date.
> the MAX() of these two dates is also subtracted from the total days and
> you get the days within each month!
>
> Plugging in values
> If you have an "IN" date of 29-Jan-2017
> and an "OUT" date of 15-Feb-2017
> and you're looking for the days worked in February of 2017:
>
> I counted the number of days from 01-Jan-2016 and 01-Jan-2018 (731)
> The days from February 1 to 01-Jan-2016 is 397, the days from the "IN"
> date to 01-Jan-2016 is 394, so the MAX() is 397
> The days from first of the next month (01-Mar-2017) to 01-Jan-2018 is 306
> The days from the "Out" date of 15-Feb-2017 to 01-Jan-2018 is 320
> so the MAX() here is 320.
> Taking total days available 731-397-320 gives 14.
>
> Now, I have a slight issue with the total here.
> since 15-Feb-2017 should be FIFTEEN days in February, not 14.
>
> It turns out that in the month of the "OUT" date, the calculation will be
> off by 1.
> so, I check to see if the month and year of the "out" date is the same as
> the "evaluation" month and if they match, I add 1:
> IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
>
> the formula ends up looking like:
> =MAX(DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
> -MAX($B3-DATE(YEAR(NOW())-1,1,1),H$2-DATE(YEAR(NOW())-1,1,1))
> -MAX(DATE(YEAR(NOW())+1,1,1)-$C3,DATE(YEAR(NOW())+1,1,1)-
> DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31))),0)
> +IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
>
> hope this helps
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Saturday, September 23, 2017 12:45 PM, vikas khen 
> wrote:
>
>
> Hi All,
>
> Request to all of you that kindly find the attached sheet and help me to
> calculate  Month wise cost by selecting the month and how to represent the
> same in piot table too.
>
> your assistance in this regards will be highly appreciated
>
> Thank you
>
> *Regards,*
> *Vikhen*
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread

Re: $$Excel-Macros$$ Formula for Month wise calculation

2017-09-29 Thread Rabindra Thapa 
Dear Mr. Paul,
Thanks :)

*Regards,*



*For quick responds click here for Message *

On Fri, Sep 29, 2017 at 3:18 PM, Paul Schreiner 
wrote:

> This is pretty "convoluted" logic.
> I tried several approaches that soon became extremely complex (as if this
> isn't?)
>
> The approach is this:
> Establish a starting date of 01-Jan of LAST year
> and an Ending Date of -1-Jan of NEXT year.
> (I could've hard-coded these dates, but then you'd have to edit the macro
> in a few months!)
>
> then, I calculate the number of days between those two dates:
> DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
>
> Next, calculate the number of days between this year old starting date and
> the "IN" date.
> $B3-DATE(YEAR(NOW())-1,1,1)
> Now, for the month you're evaluating, find the number of days since the
> first day of the month and this old starting date:
> H$2-DATE(YEAR(NOW())-1,1,1)
>
> I select the MAX() of these two dates and subtract it from the total days.
>
> I do the same thing, calculating the days between the "future" date and
> the "Out" date
> and compare it to the first day of the NEXT month from the reporting month.
> (I have to take into account that the "next" month could also be January
> of the next year, so I use:
> DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31)) to calculate that date.
> the MAX() of these two dates is also subtracted from the total days and
> you get the days within each month!
>
> Plugging in values
> If you have an "IN" date of 29-Jan-2017
> and an "OUT" date of 15-Feb-2017
> and you're looking for the days worked in February of 2017:
>
> I counted the number of days from 01-Jan-2016 and 01-Jan-2018 (731)
> The days from February 1 to 01-Jan-2016 is 397, the days from the "IN"
> date to 01-Jan-2016 is 394, so the MAX() is 397
> The days from first of the next month (01-Mar-2017) to 01-Jan-2018 is 306
> The days from the "Out" date of 15-Feb-2017 to 01-Jan-2018 is 320
> so the MAX() here is 320.
> Taking total days available 731-397-320 gives 14.
>
> Now, I have a slight issue with the total here.
> since 15-Feb-2017 should be FIFTEEN days in February, not 14.
>
> It turns out that in the month of the "OUT" date, the calculation will be
> off by 1.
> so, I check to see if the month and year of the "out" date is the same as
> the "evaluation" month and if they match, I add 1:
> IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
>
> the formula ends up looking like:
> =MAX(DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
> -MAX($B3-DATE(YEAR(NOW())-1,1,1),H$2-DATE(YEAR(NOW())-1,1,1))
> -MAX(DATE(YEAR(NOW())+1,1,1)-$C3,DATE(YEAR(NOW())+1,1,1)-
> DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31))),0)
> +IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
>
> hope this helps
>
> *Paul*
> -
>
>
>
>
>
>
>
> *“Do all the good you can,By all the means you can,In all the ways you
> can,In all the places you can,At all the times you can,To all the people
> you can,As long as ever you can.” - John Wesley*
> -
>
>
> On Saturday, September 23, 2017 12:45 PM, vikas khen 
> wrote:
>
>
> Hi All,
>
> Request to all of you that kindly find the attached sheet and help me to
> calculate  Month wise cost by selecting the month and how to represent the
> same in piot table too.
>
> your assistance in this regards will be highly appreciated
>
> Thank you
>
> *Regards,*
> *Vikhen*
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at https://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread

Re: $$Excel-Macros$$ Formula for Month wise calculation

2017-09-29 Thread Paul Schreiner 
This is pretty "convoluted" logic.I tried several approaches that soon became 
extremely complex (as if this isn't?)
The approach is this:Establish a starting date of 01-Jan of LAST yearand an 
Ending Date of -1-Jan of NEXT year.(I could've hard-coded these dates, but then 
you'd have to edit the macro in a few months!)
then, I calculate the number of days between those two 
dates:DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
Next, calculate the number of days between this year old starting date and the 
"IN" date. $B3-DATE(YEAR(NOW())-1,1,1)Now, for the month you're evaluating, 
find the number of days since the first day of the month and this old starting 
date:H$2-DATE(YEAR(NOW())-1,1,1)
I select the MAX() of these two dates and subtract it from the total days.
I do the same thing, calculating the days between the "future" date and the 
"Out" dateand compare it to the first day of the NEXT month from the reporting 
month.(I have to take into account that the "next" month could also be January 
of the next year, so I use:DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31)) to 
calculate that date.the MAX() of these two dates is also subtracted from the 
total days and you get the days within each month!
Plugging in valuesIf you have an "IN" date of 29-Jan-2017and an "OUT" date of 
15-Feb-2017and you're looking for the days worked in February of 2017:
I counted the number of days from 01-Jan-2016 and 01-Jan-2018 (731)The days 
from February 1 to 01-Jan-2016 is 397, the days from the "IN" date to 
01-Jan-2016 is 394, so the MAX() is 397The days from first of the next month 
(01-Mar-2017) to 01-Jan-2018 is 306The days from the "Out" date of 15-Feb-2017 
to 01-Jan-2018 is 320so the MAX() here is 320.Taking total days available 
731-397-320 gives 14.
Now, I have a slight issue with the total here.since 15-Feb-2017 should be 
FIFTEEN days in February, not 14.
It turns out that in the month of the "OUT" date, the calculation will be off 
by 1.so, I check to see if the month and year of the "out" date is the same as 
the "evaluation" month and if they match, I add 
1:IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
the formula ends up looking 
like:=MAX(DATE(YEAR(NOW())+1,1,1)-DATE(YEAR(NOW())-1,1,1)
-MAX($B3-DATE(YEAR(NOW())-1,1,1),H$2-DATE(YEAR(NOW())-1,1,1))
-MAX(DATE(YEAR(NOW())+1,1,1)-$C3,DATE(YEAR(NOW())+1,1,1)-DATEVALUE(MONTH(H$2+31)&"/1/"(H$2+31))),0)
+IF(AND(MONTH($C3)=MONTH(H$2),YEAR($C3)=YEAR(H$2)),1,0)
hope this helps
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
- 

On Saturday, September 23, 2017 12:45 PM, vikas khen  
wrote:
 

 Hi All,
Request to all of you that kindly find the attached sheet and help me to 
calculate  Month wise cost by selecting the month and how to represent the same 
in piot table too.
your assistance in this regards will be highly appreciated
Thank you
Regards,
Vikhen-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


   

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted

$$Excel-Macros$$ Formula for Month wise calculation

2017-09-23 Thread vikas khen 
Hi All,

Request to all of you that kindly find the attached sheet and help me to
calculate  Month wise cost by selecting the month and how to represent the
same in piot table too.

your assistance in this regards will be highly appreciated

Thank you

*Regards,*
*Vikhen*

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


Book1.xlsx
Description: MS-Excel 2007 spreadsheet