time calculation

[rachee]

I have the following portion of a spreadsheet:

CP = 00:15

ready
time rank start end
9:30 4
8:30 2 8:30 8:45
8:00 1 8:00 8:15
8:35 3 8:45 9:00
10:00 5

The "ready time" is calculated from a spreadsheet schedule and cannot be resorted. The rank is calculated using vlookup from the same schedule. The rank changes based on the "ready time".

I need for excel to (1)recognize the rank (1,2,3,...), (2)locate the first "1" rank time and place it into the "start" column, (3)add the "start" time to the "CP" time and place it in the "end" column
(8:00 + 00:15 = 8:15). (4)Excel should then recognize rank "2". If the "end" time of rank "1" is less than the "ready time" for rank "2" then the "ready time" should be used in the "start" time
for rank "2" (8:15<8:30 then use 8:30). (5)The "CP" time is to be added to rank "2" "start" time and put it into the "end" column (8:30+00:15=8:45). (6)Excel should then recognize the next rank "3".
(7)If the "end" time of rank "2" is greater than the "ready time" of rank "3" then use the "end" time of "2" as the "start" time of "3" (8:45>8:30 then use 8:45). (8)Add the "CP" time to it to get the
end time (8:45+00:15 = 9:00). Continue in this fashion until all times are filled.

[x65140]

is this what you're looking for?

[rachee]

I'm afraid not. The cell data is more dynamic than what your solution reflects. The solution would have both the "start" and "end" cells recognize the change in rank in order to sum the values in the order of rank and based on the succeeding rank's "ready time and the "end" times. I believe the solution will require a vb script solution but I'm not versed enough to develop it.

Thanks for your help

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sktneer]

Please find the attached sheet to see if this works as per your requirement.

[rachee]

excelforumfile.xlsx
please note that:
the "before" tab is the fully functioning spread sheet.
the "after" tab is the section needing help only. i've also hilited this in "before"
"F9" will change the data (times and place in queue)
as the data changes the "start" and "end" times must reflect these changes
if the "end" time of the preceding "place in queue" time (say #3) is greater than the next "ready time" (#4)then use #3's "end" time
otherwise use #4's "time ready"

[rachee]

Attachment 340884
please note that:
the "before" tab is the fully functioning spread sheet.
the "after" tab is the section needing help only. i've also hilited this in "before"
"F9" will change the data (times and place in queue)
as the data changes the "start" and "end" times must reflect these changes
if the "end" time of the preceding "place in queue" time (say #3) is greater than the next "ready time" (#4)then use #3's "end" time
otherwise use #4's "time ready"

[rachee]

one more thing, the "end" time (starting at L30) should be the "start" time + the "CP time" (cell B14)

[endah]

Hi Everyone,

I have an excel spreadsheet where the time is represented as 1.30 and I want to convert it to 1:30,can you please help me to do this.

[zbor]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[endah]

My apologies,how I do I start a new thread?