Calculating Target Date/Time Based on Start date and hours - Excel 2007

Hi,
I need a help to calculating Target date and time based on start date/time and hours. I'm explaining in detail below,

In cell A2 having start date with time(format: mm/dd/yyy hh:mm)
In cell B2 having total hours to complete the task(format: integer)

If we updating the above values in respective cells, automatically cell C2 calculate the End Date with time.
Please note that:
* the end date should exclude weekends(Saturday and Sunday), public holidays(defined in cell range D2:D10) and off working hours(i.e: Business hours of the working day is 10:00AM to 06:00PM)
* The end date should not shows as after 06:00 PM of the date

I need a solution with excel formula or vba macro with loop(preferred)

Thanks in advance,

-Chinraj-

Thanks for the reply. I'm searching the forum. But not able to get exact requirement. Since most of the post are said calculating hours between two dates.. But my case is like calculating end date based on input date and hours.

Please Help Me.

Thanks lot.. this is the thing i want.. it works perfect! awesome

Hi daddylonglegs,

I have similar question with chinraj, but in my case I have start time 08:00 and end time 17:00 and also break time from 12:00 to 13:00 (1 hour).
Please help me.

Thanks in advance,
McNazar

Many thanks DaddyLongLegs! This is exactly the formula I need. I need to understand how it works by doing more research but having a working formula is a big help!

Daddylonglegs Can't thank you enough for this. I have one small issue with the very same formula and I wanted to know your thoughts on this.
The enddate & time does not calculate correctly if the time is 2:00 PM and the duration is 4 hours. Let me explain...

StartDate&Time = 24 Sep 2:00 PM
Duration = 4 hours
WorkHours is 10:00 to 18:00

EndDate&Time shows as 25 Sep 10:00 AM instead of 24 Sep 06:00 PM

I'm racking my brains over this and can't figure out why it doesn't calculate correctly for 2:00 PM (any other time between 6-18:00 works fine). Your help will be greatly appreciated.

@praddsouza

Welcome to the Forum, but please read the Forum Rules. You will see that Rule 4 states that you should not try to hijack a thread by posting your own question in it. Instead, you should start your own thread, with a link back to this one if you think it is necessary.

Also, you do realise that the thread is over 4 years old?

Pete

Pete_UK I apologize, I was under the assumption that if the solution provided doesn't work in the normal scenario then I need to use the same thread to inform the user who provided the solution. My bad, will start a new thread.

Hi Guys,

Please help me. I would like to create a reminder sheet for my employees.

But the below formula which was provided by expert daddylonglegs is counting before the working hour


Cell A2 has TIme and Date :
Example1 : 12/9/2019 09:00 (Monday 9PM)


Cell B2 has 4 (Integer)

Cells E2 and F2 has working hours 10:00 & 19:00 Respectively

Using the below formula in C2 is giving me Result as 12/9/2019 13:00

It should give me 12/9/2019 14:00 because the business hour starts at 10:00 AM. So the target deadline should be counting from the business hour

HALP !


Formula : =WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-E$2)/(F$2-E$2),1)-1,D$2:D$9)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-E$2,F$2-E$2)+F$2-E$2

See Post #9, and note that the thread is now a year older.

Pete

Pete_UK


Hi Pete,

Can you provide me with the link, I'm new here

The post number is shown on the extreme right in each dark blue banner, so just scroll up to see Post #9.

Pete