Hello!
I've run into an issue with an Excel spreadsheet that was passed to me from a predecessor on this project. I am a project manager, and I generally use MS Project for my project plans, but my predecessor on this large account preferred to use Excel because of some of the flexibility received with various calculations.

Our "spreadsheet" project plan uses lots of date formulas to calculate workdays, starts dates, and completion dates for various tasks. The issue I've discovered is that although our summary tasks are properly calculating the overall beginning and end for a series of tasks, the subtask start and end dates are incorrect.

Here's what appears to be happening. To calculate the start date for a subtask, we use the end date of the task before it (these are all simple start-finish relationships with no lags). To calculate the end date for the task, it looks at the total number of workdays (calculated using the WORKDAY formula). Excel then adds the workdays to the start date and generates an end date (taking into account weekends and holidays that I've programmed in).

The issue is that when task 1 is 2.5 days, it technically ends at mid-day of the third day. So task 2 should start at mid-day of the third day, but Excel shows the start as the start of the third day. Task 2 is only .8 days long, so Excel shows the end date as STILL the third day when it should have rolled over to the fourth day by now.

Is there a way to get Excel to take into account the fractions of days when calculating the end date? I tried changing the display format, but all that did was reveal that Excel showed the end date as midnight of the particular day (time stamp was 00:00:00). Of course, I want excel to consider just an 8 hour day as well, and I am wondering how I can get it to accomplish that. Even if it counts partial days, it looks like it would need 24 hours to push the clock over to the next day, right?

So...any ideas on how I can make this happen? I tried using the WORKDAY formula and summing the cumulative duration of the task in question and the other tasks before it, but Excel would accept a WORKDAY formula like =WORKDAY(I14,F12:F14,Holiday).

Whatever help you could give would be most appreciated. Thanks!