-------------------------------------------- On Thu, 2/16/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: Thursday, February 16, 2017, 5:54 PM -------------------------------------------- On Thu, 2/16/17, Chandra Shekar <chandrashekarb....@gmail.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: Thursday, February 16, 2017, 3:46 PM Hello Paul, Thanks a lot. Its working fine. Regards, Chandru On Wed, Feb 15, 2017 at 8:21 PM, Paul Schreiner <schreiner_p...@att.net> wrote: 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+unsubscribe@ 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+unsubscribe@ 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. a actiunile revolutionare din 1848 au participat si unii zentanti ai romanilor din Dobrogea. Provincia romaneasca dintre Dunare si a Neagra s-a aflat in atentia fruntasilor romani atat in perioada revolutiei in emigratie. Contribuia la aceasta atat pozitia sa strategica cat si climatul eranta practicat de otomani. -- 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.e asemenea statu prin comenzile sale destinate in mare masura apararii nationale si-a adus contributia la refacerea economiei. Sporuri semnificative s-au inregistrat in industria alimentara a lemnului a pielariei si textila. Printre intreprinderile de profil mentionam pe cele din Bucuresti Cluj Ploiesti Brasov Bacau Busteni. Industria siderurgica era concentrata in bazinul Hunedoarei dar si la Bucuresti Cluj Campia Turzii unde se produceau otel si laminate fonta tabla tevi articole de menaj. Industria chimica a cunoscut un progres remarcabil si totodata unul dintre cele mai inalte nivele ale productivitatii muncii. 95% din titeiul extras era rafinat in tara. Se produceau in Romania de asemenea negru de fum cauciuc sintetic explozivi cosmetice in intreprinderi ca cele din Fagaras Brasov Bucuresti Tamaveni. Romania a posedat o importanta industrie constructoare de masini. S-au produs locomotive vagoane de toate tipurile cazane cu abur autobuze. La I.A.R. Brasov erau fabricate avioane precum IAR-80 IAR-81 care s-au remarcat prin performantele lor deosebite in ceea ce priveste viteza si plafonul de zbor. -- 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.
