Picking from a list

[Krat]

I have column with scheduled times in it ( J1- 09:00, J2 - 10:15, J3 - 12:00, J4 - 15:00 )
in F1 I have a formatted time entered, I need the time in F1 to pick the next higher number from Jlist and populate it in G1, any ideas I am stumped ?( and new )

[mikeTRON]

Welcome to the forum.

Can you 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 then scroll down to Manage Attachments to open the upload window.

[Tony Valko]

Try this array formula**:

=IF(F1>=J4,"None",MIN(IF(J1:J4>F1,J1:J4)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

EDIT: Format as Time

[Krat]

I am trying to send over sample, as soon as i figure out why its no letting me I will
thansk for the advanced tip, but the file is not showing uo when I click on the attach tab, Ill get it out there soon

[Krat]

Not sure what I missed but using this formula I was only able to get it to say "NONE", did I miss something ?

[mikeTRON]

Yeah attaching a file is a bit clunky.

Go to advanced.
Scroll down to MANAGE ATTACHMENTS.
Choose the file to upload.
Hit OK to choose it.
THEN hit UPLOAD on the right to finally upload it to the post.
THEN hit CLOSE window.

Then post the message.

[Tony Valko]

Not sure what I missed but using this formula I was only able to get it to say "NONE", did I miss something ?

Did you enter the formula as an array formula?

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

Here's a sample file that demonstrates this.

[Krat]

I added teh array and that did not seam to help , I finnaly figuered out how to attach so there is a sample on my origanl post, but I forgot to add other times to column J , so just add 5 more times down the colum in J and then the goal is to have the time in F1 select from the list in J and populate in G1

and thanks for not givin up on me yet : )

[Tony Valko]

The formula in the sample file was:

=IF(F1>=J1,"None",MIN(IF(J1:J5>F1,J1:J5)))

When I added these times to column J:

J2: 10:15
J3: 12:00
J4: 15:00

And adjusted the formula to include those additional times:

=IF(F1>=J4,"None",MIN(IF(J1:J4>F1,J1:J4)))

The formula returned the correct result of 12:00.

It might be more intuitive to write the formula like this:

=IF(F1=MAX(F1,J1:J4),"None",MIN(IF(J1:J4>F1,J1:J4)))

Still array entered.

[Krat]

Excellent, that did it, thank you, I can now start my weekend ; )

[Tony Valko]

Good deal. Thanks for the feedback!

[Krat]

Sorry, looks like I may have spoken to soon, I attached the file with the new formula and it works right up until it crossed 00:00 or midnight, meaning when you enter the time into A3 the formula works in F3 just perfect until you enter the time of 20:00 to 23:59 in A3, durning those times F3 returns with 00:00, when it should return 09:00, any ideas?

[Tony Valko]

It's because 23:15 + 1:00 = 1+12:15. In other words, it spans past midnight into the next day so the true value is 1d 12:15 but the cell is only displaying 0:15.

Also, you have 2400 (2 thousand 4 hundred) entered in J1. In Excel there is no time of day as 24:00 (or 2400).

[Krat]

Ok good point, i changed the 24:00 to 00:00 and it returns"none", so I tried setting it to 00:01 and same result "none"?

[Tony Valko]

Try changing the formulas in B3:E3.

B3: =MOD(A3+TIME(0,15,0),1)
C3: =MOD(B3+TIME(1,0,0),1)
D3: =MOD(C3+TIME(2,0,0),1)
E3: =MOD(D3+TIME(1,0,0),1)

[Krat]

You guys are frickin geniuses, thanks a bunch, that did it and now I can say, IF FRIDAY ; )

Really thanks allot for all the help

[Tony Valko]

You're welcome!