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