Excel - calculate a time field, based on another time field, and criteria

Question

Hi,

Lets say we have a scenario of test types. We have tests 1-8 Each of these tests required a delay time, but we want to calculate the time we start the test, based on the time we want to finish this test.

So lets say Test 1 requires a finish time of 1pm, but has a 1 hour delay. How can I add 1pm to field, select my test type as 1, and then have it return, in another cell, a start time of 12pm.

Likewise Test 2 requires a finish time of 1pm, but has a 3 hour delay. How can I program excel to look to the test number field and the finish field to calculate the required start time? The tests will always have a static (but different each test) delay time.

Test number	Start Time	Finish Time
1		1:00pm
2		1:00pm

Thanks for your assistance.

Answer 1

Hi @SarSto

The tests will always have a static (but different each test) delay time.

Can you list the delay time for each test, like the follows?

If yes, you may try the formula to get the Start time, in my example the formula could be =TEXT((HOUR(G2)-VLOOKUP(E2,A$1:B$3,2,TRUE))/24,"hh:mm AM/PM")

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---