--------------------------------------------
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.
