Time formula to calculate end time

0

I am looking to use a formula to calculate an end time for work. I have a start time, say 9:00am and I have a formula which calculates the number of hours/minutes it should take to finish the job. Now I just need to have a formula that takes the start time + the number of hours/minutes it should take and have it give me the estimated end time. I have tried playing with the Time formulas and it doesn't seem to give me the right time back. 

Start time is 9am, say it takes 4.6 hours to complete, it should give me a time of 1:30ish but that is not what I get. The start time is always a fixed time and I have the formula for the hours needed to complete the job in one cell. Any ideas? Thank you so much!

Answer
Discuss

Answers

0

The easiest way to go about this is to understand that Excel treats a time as a variable of Double type, meaning a number with decimals, for example 42989.375. Here 42989 is today's day (Sept 11, 2017) which started at 00:00 hours. The decimal fraction (0.375) represents the time which has lapsed in this day as Excel counts toward day 42990. Convert to hours by 0.375 * 24 = 9 meaning 9:00am.

Similarly, your 4.6 hours can be converted to a fraction of a day: 4.6 / 24 = 0.196666667 which you can conveniently add to either 0.375 or 42989.375 (depending upon your interest in time only or date and time) to arrive at 0.566666667 which you can convert to hours passed in this day by multiplying with 24 = 13:36

Excel does this conversion in the background following number format you set. Enter = 4/24 in a cell. While the number format is "General" you will see 0.19666667. Change the number format to "Time" and you will see 04:36. Change the format to Date/Time and you will see something else again - predictably, the date and time.

Accordingly, if you have the start time in one cell as 0.375 and a duration of production in another as 0.196666667 you can calculate the completion time by simple addition, as you have in fact tried. The trick is (a) not to be confused by what you see displayed in any of the three cells and (b) not to confuse Excel by what you enter, either. Once you enter 4.6 as a number of hours you must remember to convert this number into a fraction of a day before you can process it as such. On the other hand, if you enter 4:36 (with a colon as separator) Excel will understand this to be a time (depending upon your Regional settings), automatically set a Time Numberformat for the cell where you entered it, and give the cell a value of 0.19666667 which is displayed as 04:36.

You can always get Excel to display the real value of a cell by expressly setting the Numberformat to "General". Mind that, by Excel rules, the "General" format instructs to guess at what you entered and apply a suitable format automatically. So, while a cell's format is "General" to begin with entering a value may change its format. But you can change the automatically set format back to "General" to override Excel's automation.

Discuss
0

For most situations, we typically use a combination of Military time and decimals (rounded to each quarter of an hour).  So, 9:00 translates to 9.0;  9:15 ->9.25; 1:45 pm -> 13.75.  This eases the math but may have difficulties if it spans longer than a day or if times starts before 12 am and  ends the following day. ( to convert to military time add 12 (hours) to p.m. hours till midnight. 2:45 pm is 14.75.

Discuss

Discussion

So if I have a cell which has the start time of 9:00am, and I have a cell that has the time amount that the employee should work such as 4.67 hours. I have a cell that I want to display the start time (9:00am) plus the amount of time needed (4.67) which will equal the time the employee should be done at (1:30ish). How do I make that happen? Using military time or other?
kcguru Sep 13, '17 at 1:07 pm
Add to Discussion

Answer the Question

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