Jump to content

Working Days


This topic is 6451 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello all,

I have been searching the archives for answers -- and have found information, but some is a bit old, and I wonder if there might be a newer solution.

What I need to do is fairly simple, and common: Calculate the # of working days only (excluding weekends) between two dates. There are two scenarios: date1 -> date2, and date1 -> current date.

Could anyone advise of a solution or point to an existing topic thread?

many thanks in advance.

julia

Link to comment
Share on other sites

Julia has the regular edition, so a custom function is not an option.

However, there are several solutions on work calculations. You need to consider if you want the days inclusive or not, and to allow for holidays, etc.

Do a search for +Work +Day +Calculations, or +elapse +time and you should find plenty of useful help. Also, BobWeaver posted a solution in Sample Files a long time ago that shows how to work in holidays, and has kept it update over the years.

HTH

Lee

Link to comment
Share on other sites

Hi Julia,

There is no difference between Date1 and Date2 vs. Date1 and current date. The only difference is that, when using Get(CurrentDate), the calculation must be unstored (in calculation box, select Storage Options and check 'Do not store...'). Most date-span requests are inclusive, ie, From/THRU and not From/To. When viewing a full week on a calendar, User wants to count 5 work days and not begin counting on Tuesday - Friday. This calc is inclusive but if needed, it can be easily adjusted by adding a User global toggle to drop the last day (depending upon whether workday or weekend). If you wish the end date to be [color:blue]Get ( CurrentDate ) then just replace all instances of Date2 with this entire portion in blue (with parenthesis intact).

If ( Date1 and Date2 ) ;

Let ( [

spanStart = Date1 - Mod ( Date1 ; 7 ) ;

spanEnd = Date2 - Mod ( Date2 ; 7 ) + 6 ;

wkndCount = Div ( spanEnd - spanStart + 1 ; 7 ) * 2 - (spanStart < Date1 ) - ( spanEnd > Date2 )

] ;

Date2 - Date1 + 1 - wkndCount

)

)

The span starts at the first Sunday prior to the start date and the span ends on Saturday after the end date. Weekend count is the full weeks in between then boolean test deducts one day if span starts before Date1 (indicating first day of span is Sunday and NOT within the range) and deducts one day if span ends after end date (indicating last day of span is Saturday and not within the range). Once we know the weekend count, we subtract weekend count from total days in the (inclusive) date range.

Oh ... this doesn't require custom function and this is the only calc required.

Link to comment
Share on other sites

This topic is 6451 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.