• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate an end date given a 6- and 7-day workweek?

dronka

dronka

Member
I'm using the Workday function in a workplan spreadsheet to calculate the due date of tasks given the start date, the number of days required for the task, and holidays. This assumes a 5-day work week.

I'd like to also calculate due dates based on a 6- and 7-day workweek.

I found the Networkdays.Intl function, which I thought addressed this issue. However, unlike the Workday function, you have to enter a begin and an end date. The problem is, I'm trying to solve for the end date given a specific number of days required to do the task.

Can anyone think of how to use this function (or another function) to calculate an end date given start date, number of days required, holidays, and the ability to specify which days are working days and which days are non-working days?
 
Hello,
Have you tried the WORKDAY.INTL() function? That allows you to specify what a weekend is, including specifying a single day as weekend.

Can you clarify what you mean by 6-day and 7-day work week? In such a work week, is there still a "weekend"? If so, how many days comprise that weekend?

Please give an example of a 6-day and 7-day work week.

-Sajan.
 
I think this is exactly what I'm looking for, Sajan! I didn't realize WORKDAY had a .INTL version. A 6-day workweek would be something like Sundays off, and a 7-day workweek would be without any days off (who'd want to do that, eh?!).
 
Glad you solved your question!

WORKDAY.INTL() was added in a recent version of Excel (I don't remember which version).

By the way, which industry uses a 7-day workweek (without any days off)? (I want to make sure I avoid them as part of any job searches!!)
 
Sajan - It doesn't look like Excel 2007 (which is what I and my colleagues use) has this function. Do you know of a workaround for this?
 
Hi,
I do not have Excel 2007, and so am not able to verify this... but you can try the following array formula:
=StartDate-1+MATCH(DaysToIncrement, MMULT((ROW(OFFSET(A$1,,,20))>=COLUMN(OFFSET(A$1,,,,20)))*TRANSPOSE((WEEKDAY(ROW(INDEX(A:A,StartDate):INDEX(A:A,StartDate-1+20)))<>7)*ISNA(MATCH(ROW(INDEX(A:A,StartDate):INDEX(A:A,StartDate-1+20)),Holidays,0))), ROW(OFFSET(A$1,,,20))^0))

enter with Ctrl + shift + Enter

Replace 20 with a larger number if you expect to retrieve dates beyond 20 days from StartDate
<>7 represents a comparison to Saturdays.

Cheers,
Sajan.
 
Luke - What's a UDF code? Would I copy and paste Chip's code into a VBA module and it would then create a new function?
 
Sajan - Thanks for the array. I will try it out. I have no idea what some of those functions are, but if it works, it works, right??
 
dronka said:
Luke - What's a UDF code? Would I copy and paste Chip's code into a VBA module and it would then create a new function?
Click to expand...

UDF = User Defined Function. Yes, you could just copy and paste into a new regular module, and then you cna call the function from either the VB or the worksheet.
 
You must log in or register to reply here.
Back
Top