-------------------------------------------- On Wed, 2/15/17, keisha.fry via MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> wrote:
Subject: Re: $$Excel-Macros$$ Difference between times(Including and excluding weekends with Business hours 9 AM to 8 PM) To: excel-macros@googlegroups.com Date: Wednesday, February 15, 2017, 5:11 PM -------------------------------------------- On Wed, 2/15/17, Paul Schreiner <schreiner_p...@att.net> wrote: Subject: Re: $$Excel-Macros$$ Difference between times(Including and excluding weekends with Business hours 9 AM to 8 PM) To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Date: Wednesday, February 15, 2017, 4:51 PM There are several ways to approach this.Here's the logic I used:first: Excel date is stored as a number. (the number of days since 1/1/1900)Excel TIME is stored as the decimal part of a day.so, the date/time: 19/11/2016 12:10 PM is actually 42693.50694Excel allows you to DISPLAY this value in a variety of ways, but it doesn't change the value. That means that to determine the time of day without regard to the date, you must subtract the "integer" portion of the value. Now, if you worked from Monday, 2-Feb to Friday, 10-Feb, you expect the result to show you worked 5 days.However, simply subtracting day #42772 from day #42776 gives you a DIFFERENCE of 4 instead of 5!So, you have to be careful with date arithmetic... Excel provides two functions:NetworkdaysNetworkdays.intl Networkdays.intl provides a means to define which days are your "weekend".(some people may work Thu-Fri-Sat-Sun-Mon and have Tuesday and Wednesday off for their "weekend")This also provides a means to say ALL days are workdays! (thereby, "including weekends"). In the file attached, I created a sheet with Named Ranges for Start_Time, End_Time and HolidayListYou can add/remove dates to this HolidayList as appropriate to your needs. The logic is to first calculate the number of workdays between the Start date and End data.=NETWORKDAYS.INTL($A2,$B2,"0000011",HolidayList)Now, the End_Time - Start_Time gives you the fractional part of a day that represents a "full" workday.Multiplying this times the number of workdays results in the number of hours if full days are worked. This number needs to be adjusted based on the actual start/end time.If the start or end day is on a weekend, then the actual start/end time is disregarded.IF(WEEKDAY($A2,2)>5,0 Otherwise, for the start time, subtract the actual start time (A2 - int(A2)) from the scheduled "Start_Time".Now, if the person started BEFORE the scheduled start time, this value will be negative and should be disregarded. (unless you wish to calculate "overtime", or allow for a person to come in 30 minutes early and leave 30 minutes early?) so, to use only positive numbers, you can use: MAX(($A2-INT($A2))-Start_Time,0) For End time, the arithmetic is reversed: MAX(End_Time-($B2-INT($B2)),0) So, calculating the full working days, and removing the adjustments for start/end time, you get:=NETWORKDAYS.INTL($A2,$B2,"0000011",HolidayList)*(End_Time-Start_Time) -(IF(WEEKDAY($A2,2)>5,0,MAX($A2-INT($A2)-Start_Time,0))) -(IF(WEEKDAY($B2,2)>5,0,MAX(End_Time-($B2-INT($B2)),0))) But if you wish to use it as a number of hours, you'll need to multiply it by 24. To calculate these values and INCLUDE weekends, simply change the Networkdays.Intl formula to include "0000000" and remove the IF(WEEKDAY( test:=NETWORKDAYS.INTL($A2,$B2,"0000000",HolidayList)*(End_Time-Start_Time) -(MAX($A2-INT($A2)-Start_Time,0)) -(MAX(End_Time-($B2-INT($B2)),0)) Note: What this technique does NOT do is check to see if the Start or End date is one of the listed holidays.Nor does it account for any time outside of the core "Business Hours". 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 Wednesday, February 15, 2017 7:17 AM, Chandra Shekar <chandrashekarb....@gmail.com> wrote: Hello, Needed difference between times(Including and excluding weekends with Business hours from 9AM to 8PM) Could you help me on this attachment. Regards, Chandru -- 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. 0Walewski ministrul de externe al Frantei premierul englez Palmerston. Portii otomane i-au fost trimise repetate memorii de protest in care erau infatisate realitatile romanesti si dorinta unanima de infaptuire a statului national. Au fost antrenate in sprijinul acestei idei si spiritele alese ale vietii publice si culturale europene intre care s-au remarcat Jules Michelet Edgar Quinet J.A. Vaillants\ multi altii care au pledat cu caldura pentru cauza noastra. -- 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.onservatoare. La 15 27 iunie pe Campia de la Filaret masele populare -- 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.
