Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculating a Start date/time by using Due Date and Hours

0

Hello, I'm trying to calculate the Starting date and time of a project using the End Date(Due by Date) and the estimated hours needed.  I am also trying to exclude weekend days and I only count hours within my start and end time for each day.

I'm adding a reference to a previous question where the End Time was calculated this way from the Start time and estimated hours, but I am having trouble modifying this to achieve my goals.  Any help would be appreciated, I am using Excel 2007.

The previous question is linked here: https://www.teachexcel.com/talk/989/calculation-of-end-date-by-using-start-date-and-time-and-working-hours-as-i

And pasted below is the original question with the answer formula:

 Here incident is created at particular date and time .We set some priorties          priority  severity resolve issue in time                   p1 s1 4 hrs                   p2 s2 9hrs                   p3 s3 27hrs                   p4 s4 45hrs                                                                         Now we need to calculate closure date by excluding saturdays and sundays         and timings 9am to 6 pm.                   if incident created on Friday evening 6 we need to calculate hrs from Monday 9 am because of weekends.   Pls solve this. Selected Answer

You are in luck! There is a great formula for this.

Select All 
=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))

In this example it is assumed that you have 4 cells setup like this:

  1. A2 is the starting date/time
  2. B2 is the number of hours
  3. E2 is the starting time of the workday
  4. F2 is the ending time of the workday

You can change the cell references as you need but be careful since it's a huge formula and you will need to change things in a few different places.

(I found this formula on the web a while back and it has worked great for me.)

posted by: don (rep: 1989) on Tue Aug 30, 2016 at 10:25 pm
exceldate-time
MS Office Version: 2007
posted by: classic80srock on Wed Apr 12, 2023 at 11:40 pm
Answer
Discuss

Answers

0

Hello classic80srock and welcome to the forum.

If you don't need to break it down to hours-minutes-seconds, but only need to know the start day, you could use the Workday function. With the Workday function you can calculate a date before or after a given reference date. (to work backwards from a reference date ("End Date") the number of days needs to be a negative number) You also have the option to specify holidays to omit.

I have attached a sample fle so you can see how this works.

Update April 20/23:

Hello again classic80srock,

Dealing with TIME in Excel is quite tricky. It took a lot of trial and error but my perseverance prevailed. I tried countless methods to calculate the start time by just using formulas but failed (that doesn't mean it's impossible, I just couldn't figure it out). I first thought changing some " +'s " to " -'s " in Don's formula would be the answer, but nope. So I split the date and time data into two separate cells and finding a solution became much easier. I also I elected to add some "helper" cells ("Project Details" in the second file - "Calculate Start Date and Time V1"). Using the helper cells meant the formulas could be shorter and easier to understand. I have attached a new file with my new solution. I tweaked the page layout a bit for appearance sake. I also added some cell comments to help with the understanding. The worksheet is password protected (no password) to guard against formulas getting corrupted or deleted. The light blue cells are not locked and can be edited.

I hope this new files answers you question. If so, please mark it as selected.

Cheers   :-)

posted by: WillieD24 (rep: 657) on Thu Apr 13, 2023 at 10:43 pm
Discuss

Discussion

Hello, thank you for helping with this.  I do need the Date and Time(in hours) for the start of the project, sorry I wasnt clear on this.
I need to do the following:
1. Take the End Date and time
2. Subtract the projected hours to find the starting date and time
3. Only count workdays
4. Only use hours within those work days.
Here is a link to the original Tip and Tutorial that shows calculating the Ending date and time from a starting date and time (only using workdays and specific hours within each day) with an explanation of the calculation used and an excel example showing the fields used.
https://www.teachexcel.com/excel-tutorial/add-time-together-limiting-by-working-hours-and-excluding-weekends-and-holidays-in-excel_1754.html
classic80srock Apr 14, '23 at 5:21 am
@ classic80srock,
Thanks for the additional details, but I won't have a chance to work on it today. It will be a couple of days before I can get back to this.
However, I do have a couple of questions.
1) Broken down to hours, no minutes & seconds, correct?
2) By including hours there are 3 possibilities: i) project starts mid-day; ii) the project ends mid-day; iii) the project start and end are both mid-day.
The estimated duration (hours) of the project must be given. And the End Date & Time (project deadline ?) must also be given.
In my opinion, working with just days would be a better way to go. By including hours that doesn't leave any "wiggle room" in the project for unforeseens. If, using your method and a start time of 1:00 PM is calculated but the project doesn't start until 2:45 PM that pushes the end time 1hr & 45min later. Whereas, using the Workday function 8-1/4 days is treated as 9 days for the project leaving 6hrs of "wiggle room" for unforeseens.
Also, when calculating the number of workdays, the length of the of the project (hours) is divided by the number of working hours (breaks and lunches are not included) in your workday

Cheers   :-)
WillieD24 (rep: 657) Apr 14, '23 at 9:17 am
Hello,
1) Yes, Hours only.  (Some projects will carry over multiple days, some can be as low as 1 hour. Each employee can work on multiple projects per day as projects are completed.) 
2) Mid-day start and mid-day end is fine, I will be comparing how many projects are needing to be worked on at one time to how many employees we have available at that same time before the projects actually get to production.  
Also, we normally don't have much "wiggle" room, projects need to be done on time(usually by 5pm for example) and are often given to us later in the day so we need to assign extra employees to get it done on time.  So if the start time is calculated before the current time when a project is given to us, we need to shift people around and add more people to essentially "cut down" the overall projected hours.(a 6 hour job with 1 person will be 6 hours, a 6 hour job with 2 people will essentially be 3 hours from start to finish.)

Correct, breaks and lunches are not calculated, that is part of our monitoring projects to make sure they are on time by shifting people around if needed or as projects finish and people become available for their next project.
classic80srock Apr 15, '23 at 12:32 pm
@classic80srock
I haven't forgotten about your problem, I've just been busy with other projects. I hope to have a possible solution by week's end
Cheers   :-)
WillieD24 (rep: 657) Apr 17, '23 at 1:50 pm
@classic80srock

See my update (April 20) to my April 13th post. I first thought changing some " +'s " to " -'s " in Don's formula would be the answer, but nope. So I split the date and time data into two separate cells and finding a solution became much easier. Hopefully my new solution is what you were looking for.
Cheers   :-)
WillieD24 (rep: 657) Apr 20, '23 at 5:29 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login