Greetings everybody!



I know that this question is rather frequent, and I looked in the net for the answer, but failed to find a suitable solution. I also searched this forum, but strangely it only finds one thread from the keyword "workday". Hence the question.

Excel 2003. I have a lot of small tasks with duration well under one day - like 0.25 day. I need to calculate the duration of sequental completion of those tasks, taking weekends into account. Here's where the problems start. WORKDAY function is famous for truncating the fractional parts of the day and I am getting nowhere with such small portions of the day as mine. Any ideas how can this problem be solved?

Hi Scaffold
Firstly, as an experience project manager of some 30 years plus I would strongly recommend you use a planning tool such as MS Project for this. However if you dont have access to MS Project then may I suggest you post a sample of your spreadsheet so we can see exactly what your isue is and we would be better prepared to help you.
Tony (OTPM)

Hi Tony!

Unfortunately, we don't have MS Project, I have to make do with Excel.

Here's the example, it's random but accurately reflects the task I need to solve. I would like to calculate the finishing date of the completion of all tasks (the one starts after another), and also the date each of the task is completed (only day, not interested in time of the day).

Thanks in advance!

Attached Files

Scaffold's Example.xls (15.5 KB, 16 views)

Hi Scaffold
Your solution is attached.
Basically Excel stores dates as the number of days since 1st January 1900. On that basis, as you have stored your individual task duration's as percentages of a day you just need to add the start date to the number of days for your first task to get the completion date of Task 1. Then for task 2 add the start date to the duration's of Task 1 and Task 2 and so on.
To obtain the end date of all the tasks simply add the start date to all the duration's of each task.
Hope this helps.
Tony(OTPM)

Attached Files

DateFinishCalculation.xls (14.5 KB, 26 views)

Hi Tony!

Thanks for the advice, but there's a problem with it. It does not take into account weekends and holidays. That's the problem! I need a solution that would work with WORKDAY function. So far I could not design around that problem with truncation within that function.

What are the working hours on each work day?

I ask because the finishing date will vary if, for example, work starts 8:00am and finishes at 6:00pm. Can we assume that the start time is 12:00am midnight and each work day is 24 hours?

__________________
Colin

RAD Excel Blog

Hi Colin!

There's 8 working hours in each day. That's why I converted days from hours as 1/8.

For the starters, we can accept your assumption and see what happens then. But if you assume that each working day lasts 24 hours, then 2 hours of my work should be converted to 1/4 of that day, right?

Thanks in advance!

Hi Scaffold
Please see attached.
This is the closest I can get to exclude weekends but we need to find a way of checking entries that are moved to the next working day to see if the total working hours are greater than 8.
Colin may be able to help on this.
Good luck.
Tony(OTPM)

Attached Files

DateFinishCalculation.xls (26.5 KB, 17 views)

Looks like I've finally found the way around the problem. Check attached file below.

Attached Files

Scaffold's Example Solution.xls (17.0 KB, 45 views)

Hi Scaffold
I don't believe this does solve your issue fully. As you advised Colin that you work an 8 hour day. In your solution you have one 112 hour day, one 15 hour day and one 16 hour day. Surely you need a solution that will carry over any hours in the same day that exceed 8 hours?
Tony (OTPM)

Hi Scaffold
I have just found some FREE planning software that will provide you with what you want without having to use Excel which is NOT a planning tool.
See the link:
http://www.planningforce-express.com...g-software.php
Good luck.
Tony(OTPM)

Hi Tony!

I see no problem with my solution. It works exactly as intended, adding fractions of days and excluding weekends. Duration of workday is accounted for by calculating of the fraction of the day it takes to complete the task. Am I overlooking something?

Thanks for the link, but in my company we have to stick to the officially approved software. Thanks for the help, anyway.