Formula: Adding Hours to Dates

I found this post on another web site.

This formula works on the basis that the start time is in the format dd/mm/yyyy hh:mm in A1 and that the hours to be added are formatted as hour and the entries to this cell made as e.g. =4/24 in B1

IF(AND(WEEKDAY(A1,2)=5,A1+B1>(INT(A1)+0.70834)),INT(A1)+3.33334+((A1+B1)-(INT(A1)+0.70834)),IF((A1+B1)>(INT(A1)+0.70834),INT(A1)+1.3334+((A1+B1)-(INT(A1)+0.70834)),A1+B1))

=

It was posted by Robert B, I’ve tried contacting him to get a syntax explanation, but as of now no response.

I’ve been working with the formula and it does work. The one problem is that it no longer cuts off at 5pm after adding 19 hours. Or, whenever you double the shift. So a 9hr work day stops working at 19 hours or a 10hr work day stops working at 20 hrs. I can’t figure out why. Anyone have any ideas on this one. I am looking for something that wont have any cut off. If that isn’t possible at least in the range of 150-200 hours. The decimals are the start time and end time for the day. What really throws me off is the “3.3334” & “1.3334”. I don’t understand why he would be adding 3 days to the date and than 1 at the end.

Could we have a hint of what you're trying to do?

If A1 is always within work hours and D2 contains the Mon-Fri start time [e.g. 08:00] and E2 Mon-Fri end time [e.g. 17:00] then you can use this formula for a projected end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+CEILING(MOD(MOD(A1,1)+MOD(B1,E$2-D$2)-D$2-0.00001,E$2-D$2)+D$2,"0:01")

format result cell to show date and time

Note: WORKDAY function requires Analysis ToolPak add-in to be enabled for Excel 2003 and earlier versions

I am trying to get the estimated completion time of a seris of production tasks. Lets say you run a 9hr business day but your task takes 24hrs. You plan to start on Monday morning at 8am. This formula will hopefully tell me that the task will be completed on Wednesday at 2pm. Using pivot tables and a few formulas I hope to create a start/completion schedule.

Daddylonglegs,

What is B1? When you say "A1 is always within working hours", what do you mean? My tasks will go over one business day.

B1 is the hours to add, as in the original formula you quoted, so if A1 is 28-Jul-2008 08:00 and B1 is 24:00 the formula I suggested will give you the result you asked for
i.e.

30-July-2008 14:00

Assuming that D2 contains 08:00 and E2 17:00

Actually I improved it slightly so it would be better to use this:

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

When I said A1 should be within work hours what I meant was that the start date/time should always be a Monday to Friday between the times in D2 and E2.

Thanks. Formula works great.

Just a note for anyone else using this formula. Remember you need to divide the hours you plan to add by 24. Otherwise excel will see the whole number as Days.

Originally Posted by Grock258

Just a note for anyone else using this formula. Remember you need to divide the hours you plan to add by 24. Otherwise excel will see the whole number as Days.

This is the same as entering the hours in time format. For example if you want to add 100 hours you can just enter 100:00 in B1