Converting Work Weeks to Workdays

Hi Group

Looking for a function that will convert Work Weeks to Workdays, factoring holidays and with or without weekends

Example:

If planning 2 work weeks, will have 10 work days if there are no holidays during these 2 weeks and it's a standard work week

Later,

I may need to plan a 6 or 7 day work week and would like to see these options

See attached example


Thanks

Try NETWORKDAY.INTL with weekend option: 0 (workday) and 1 (weekend)

With 2 weekends:
=NETWORKDAYS.INTL(B10,E10,"0000011",$H$8:$H$27)

1 weekend (Sun):
=NETWORKDAYS.INTL(B10,E10,"0000001",$H$8:$H$27)

bebo021999

These work great!

Thanks

bebo021999 & Others

Re-reviewed the function a bit more

The target date column dates are manual inputs in which I left there for reference

To clarify more

Looking to take Planned Start Date & figure out (approximate) date based on the estimated weeks

In the first example

Starting task on, 10/25/2021 with task planned for 12 weeks will give me target date of 01/17/2022

Request assistance to modify both columns I & J within my example

Thanks

Originally Posted by mycon73

Request assistance to modify both columns I & J within my example

I is holiday and J is blank. Do you mean D & E?
Start on 10/25+2 weeks, what is your expected result and where?

bebo021999

Target Dates columns (column E) were will calculate from Planned Start date & converted Weeks to Work Days to GIVE Target Dates

Hope this clarifies

What is your expected result , for instant, 10/25/2021 + 2 weeks?

I have put the definition of the work week to be used by the workday.intl() function in cell A2. It is a text string with 0 meaning a work day and 1 meaning an Off Day. Per the workday.intl() documentation the text string is 7 characters long, the first character maps to Monday, so with the string set to 0000011 this means a regular Mon-Fri work week. You can adjust this cell if you decide to go to a 6 or 7 day work week.

Your "Planned work weeks" column is, I'm guessing, your expected effort assuming a nominal 5 day week, so "Workday conversion" then is simply 5*"Planned work weeks". So in D10 copied down:

Formula:

Please Login or Register  to view this content.

Now you need to find the end date that provides "Workday Conversion" work days allowing for both weekends and holidays - that's exactly what the WORKDAY.INTL() function does, so in E10 copied down:

Formula:

Please Login or Register  to view this content.

See the attached update to your workbook.

Let us know if this is on the right track. If not, then per Bebo021999's request - please clarify and add expected results.

Hi GeoffW283

What & why is "0000011" needed?


This looks good!

In column D - Workday Conversion - Should it also be factoring holidays?

If using in Cell F10 or added column, =NETWORKDAYS(B10,E10) - It appears 1 more day is is added to almost all date durations

mycon73 please update your profile to show what version of Office you are using. Members consider this when offering solutions.

What & why is "0000011" needed?

"0000011" is the third parameter to the WORKDAY.INTL() function in the column-E formula. It specifies which days of the week are work days (the zeros) and which days of the week are time off (the ones). The string is always 7 characters long and goes from Monday to Sunday. Therefore "0000011" represents a normal Monday to Friday work week. See the Microsoft documentation for a more thorough explanation. I kept this parameter in its own cell in the event that you ever see a need to alter the working week.

In column D - Workday Conversion - Should it also be factoring holidays?

I assumed (rightly or wrongly( that Column-C "Planned work weeks" represented the estimated required effort for the project assuming a nominal five day working week. Column-D is therefore just the effort in weeks multiplied by five. That gives you the number of planned working days that need to be fitted in by the end date taking into account both weekends and holidays - that's the calculation that the WORKDAY.INTL() function in column-E performs and is where both weekends and holidays are taken into account. The column-D header might be better renamed "Planned work days"

If using in Cell F10 or added column, =NETWORKDAYS(B10,E10) - It appears 1 more day is is added to almost all date durations

Interesting. I saw no explicit explanation in the Microsoft documentation for the apparent 1 day discrepancy.

To try to see what is happening a little clearer, in C10 I entered 0.2 weeks so that the planned work days is exactly 1.

The start date is 10/25/2021 and the col-E formula computed a target end date of 10/26/2021. The NETWORKDAYS.INTL() call in col-F determined that there are 2 work days between 10/25/2021 and 10/26/2021, so who is right?

NETWORKDAYS.INTL() in col-F says 2 days - which makes sense - you have 10/25 and 10/26

But if you have 1 day of effort and you start first thing on 10/25/2021 then you would expect to finish the job by the end of the same day.

Therefore, in summary, I think it would be correct to simply subtract 1 from the current col-E formula which will make it consistent with your col-F NETWORKDAYS.INTL() check, so in E10:

Formula:

Please Login or Register  to view this content.

That does leave a single "one-day-difference" anomaly on row-22 where the start date is 7/4/2022, presumably because your start date is a defined holiday. We could complicate the col-E formula by checking for this condition or you can just avoid starting a project on a holiday day - let us know.

I hope this makes things clearer.

GeoffW283

Thank you for the detailed explanations

Administrative Note

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thanks for the feedback and rep!