Calculate date from given amount

[D Willett]

I hope I can explain this !!

On a spreadsheet i need to work out the operation length as a date.
I have a start date C10, I have days duration D10. If the start date is 01/06/2016 and a duration of 0.5 days duration then the operation length (date) should be 1 day (rounded), 01/06/2016 which is held in E10 as the result.
On the next row, the next operation should take the date from E10 (01/06/2016) as that operations start date and calculate the operation length from the value in D11 of 5 days giving an operation length (date) of 05/06/2016.
And so forth going down the list.

I need a formula to calculate this and to take into account weekends and holidays.

Please look at the attached spreadsheet, the cells which matter and need the formula's are coloured in green.

Kind Regards

[Rudi]

Hi Dave,

See if this is providing the desired output...
The WORKDAY() function calculates dates based on a given start date, a given interval and excludes weekends and holidays. So the calculated date always falls in the work week.

Operation Length.xlsx

[D Willett]

Hi Rudi
From what I can see its ok. I thought the calculation would be far more complicated as column D is numerical days ( amount ) as opposed to a date format.
So does WORKDAYS works with both a date format and an amount??? Really surprised me if so !!!

Cheeers

[Rudi]

Hi Dave,

First, I've never been to hot with date/time calcs (so take my advice with a pinch of salt).

To answer your question...

WORKDAY() has three arguments:
(1) A start date (which is the date in column E),
(2) A duration (which you have in column D = the amount of days you want to add to the start date) << This must be a number (not a date)
(3) A list of holidays (an optional argument of days to exclude from the resulting output).

Based on what you have on the sheet, WORKDAY should output correctly.
I assume it is rounding off the decimals in the D column, but I cannot be sure?

[D Willett]

Looks pretty good to me.....

Thanks Rudi

[PJ_in_FL]

If you set the duration for all tasks to 0.5 days, the end date never increments. You can't have 5 half-day tasks all complete on the same day!

If you have tasks with fractional day length durations then that needs to be accounted for in the spreadsheet. I don't have the time at the moment to work out the details but perhaps you can start by adding hours to the dates and making sure the task duration doesn't exceed the working day's hours.

[D Willett]

Hi PJ
I had thought of adding start time as well as start date but didn't want to over complicate the issue. The spreadsheet is only for my use and just used as an indicator as part of a bigger project. At some time I may add the time element which should prove to deliver accurate results. For now, it kind of ticks the box for what I need it to do.

Thanks for your comments, they've not fell on deaf ears :-)

Kind Regards