$$Excel-Macros$$ Formula for Month wise calculation
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
Re: $$Excel-Macros$$ Formula for Month wise calculation
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/"&YEAR(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/"&YEAR(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 material and the
Re: $$Excel-Macros$$ Formula for Month wise calculation
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/"&YEAR(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/"&YEAR(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
Re: $$Excel-Macros$$ Formula for Month wise calculation
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/"&YEAR(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/"&YEAR(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 P