Without knowing the nature of your data, it's hard to know the 'best' approach.
but here are some "tools" to try.
first of all, the Datediff function can tell you the number of
days,months, weeks, years, hours, minutes, seconds between the two dates.
which may be a bit more flexible than (end - begin)
Second,
the Weekday function will tell you... well... what day of the week the date is.
for instance:Weekday(Begin_dt, vbMonday) says that 2/6/2009 is day "5"
(vbmonday = 1, Tues = 2... friday = 5)
Weekday(End_dt, vbMonday) = 1, or 2/9/2009 is a Monday.
using that, you can test to see if the date is 6 or 7, or..
you can use:
If (Weekday(begin_dt, vbMonday) + DateDiff("d", begin_dt, End_dt, vbMonday) >=
6) Then
this uses the Weekday() function to determine the day number.
then the DateDiff function to determine the number of days between the two
dates.
If you take the day number and add the number of days, and the result is more
than 6,
then you're including a weekend (at least one).
At that point, you're going to have to do some more programming to determine
how MANY weekends.
also, you're going to have to decide what to do if the date is a Saturday or
Sunday.
for instance, for the beginning date, if the date is Saturday, do you shift it
to
Monday at 12:00:00am? (there are several functions you can use here)
what about end date? if it is on sat/sun, do you shift it to Friday at midnight?
what to do?
Paul
----- Original Message ----
> From: mongoose <svma...@kent.edu>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Tuesday, February 10, 2009 11:24:54 AM
> Subject: $$Excel-Macros$$ Exclude weekends in time interval calculations
>
>
> Hi, I am looking to automatically exclude weekend hours from time
> interval calculations
> Example of simple calculation including weekends:
> Begin dt = 02/06/2009 19:44:31
> End dt = 02/09/2009 10:13:41
> (End - begin)*24 = 62.48611104 hours
>
> Example of what I want without weekends:
> Begin dt = 02/06/2009 19:44:31
> End dt = 02/09/2009 10:13:41
> (End - begin) * 24 and excluding weekends = 14.48611104 (hours)
>
> I can get 14.48611104 if I subtract 48 to acct for the weekend, but I
> don't always know which dates are weekends and am hoping to identify
> them automatically in my (end - begin) calculation.
>
>
>
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
