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 <schreiner_p...@att.net>
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 <vikk...@gmail.com>
> 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 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 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.

Reply via email to