Attempting to create a formula for work that contains specific criteria for the day-to-day

[bcastle]

Hello All,

First post, but been reading a lot of threads in the past years to get help on certain functions and how to use them properly. I have a formula that I'm trying to create but am unable to link it correctly with all the formulas I am aware of.

Here is the issue. For a day to day schedule, the day starts at 0500 and ends at 1500 (3:00 PM). In my work there are many several steps in the process, some short and some long. My end goal is this:

An employee puts the Start time of the process to begin. This start time can be anytime between 0500 - 1500, the employee selects the Step they are starting at (from a drop menu), and the Sum of the times of the steps plus the Starting time will generate an end time before or at 1500 which will be displayed as the last step possible from the list A-Z.

For example: The day begins at 0500, but I begin STEP F at 1045. I would be able to get to STEP H before my shift was complete.

If this seems possible, this would be sooooooooooooooo helpful.

I thank anyone and everyone who has any input. This may seem so easy to the experts, but the time addition with the end time constraint is what is making it difficult for me.

Thank y'all!

**please excuse the fact that I typed 1130 in the start time. It should be 1045.

[BMV]

=TEXT(MIN(REPLACE(RIGHT("00"&F4;4);3;;":")+VLOOKUP(F5;E13:F38;2;)/24;--"15:00");"hhmm")

If you use time format 10:45 then it could be easy
=MIN(F4+VLOOKUP(F5;E13:F38;2;)/24;--"15:00") and time format for the cell. See Sheet1 (2)

[bcastle]

BMV,

THANK YOU!! This is a great formula but not quite what I was seeking. So, the end goal (F6) should also be displayed as a step (A-Z). In my example, I say "The day begins at 0500, but I begin STEP F at 1045. I would be able to get to STEP H before my shift was complete". So it doesn't have to be step F, but any step from the drop down (A-Z) and the formula should calculate the times associated with each step but End Goal should display the step that I could get to before my shift is done.

Another example: I begin my shift very late. I start at 1200 PM, but I will be starting with STEP B. Step B takes 0.7 Hr. So now, I'm at 1242. Step C takes 0.9 Hr, so now I'm at 1:35PM. Step D takes 0.8 Hr, taking me to 2:25PM. Step E takes 0.6 Hr to complete, taking me to 3:00PM. This is the end of my shift. So the excel sheet would only display, F4 (1200), F5 (B) and F6 (E).

This way, I could put in anytime the employee comes in, they could designate the step they will begin with, but given the time their shift ends, they could complete their work up to a specific step (designated as end goal).

Thank you again,
I know I will be able to use the formula you created as well. Hopefully, you can respond back. I do hope I clarified my initial question. YOU'RE THE BEST!!!

[BMV]

So you would like to see last step which could be finished before 1500. Let me 10 minutes
It is array formula (CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

[bcastle]

AMAZING!!!!!

Thank YOU!!!!!