Dropdown boxes and military time

[DrEd]

Hi. I have a little experience with Excel but I need to do two things beyond my expertise.

(1) I have dropdown boxes and they work fine when the box is opened and an item is selected. After an item is selected it is auto-recognized next time the first letter of a box item is typed. Is there any way to get it to auto-recognized the first time without using the dropdown box. For example, if the drop down box has words ‘blue, red, yellow, green’ it does not recognize ‘blue’ when I type ‘‘b’ until it is first selected from the menu, then subsequent entries of ‘b’ are recognized as ‘blue.’

(2) This one is more complex. I need to do T-V=W where T is military time without colons, V is another military time without colons and W is total hours and minutes. It gets further complicated because the military times can cross over two days. Such as: Tuesday night 2300 – Wednesday morning 0430 = 5:30 total minutes.

Thank you very much for any help you can provide.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[DrEd]

Attached is a brief January worksheet with the columns in questions. All other data is removed because it is unrelated to the issue. Column H is Race. The dropdown works fine when the first letter is typed but each month when the page starts new the first letter is not reognized until the entire word is typed in or the dropdown word is selected at least once. For example, if you clear contents in column H and try to type in one of the words in the dropdown for the first time, it won't auto-recognize it. It must be selected from dropdown or type in the entire word/phrase, then it will recognize subsequent partial type entires.

The other colums are T (military time) - V (military time) = W (total hours:minutes.) Note that this will often need to cross over midnight into the next morning as noted in my previous post.

Thanks for your help.

[royUK]

Your sheet is using Data Validation lists. What is happening is that Excel is recognising that you may be attempting to type a word that has already been used in the column & is offering it as a suggestion. This has nothing to do with the Data Validation, it occurs automatically in any Column. I think it is a default setting but it can be switched on or off from Tools -> Options & select Enable Autocomplete for cell values.

What you want would be available with a ComboBox but I wouldn't use that many Comboboxes in a sheet

[DrEd]

Your sheet is using Data Validation lists. What is happening is that Excel is recognising that you may be attempting to type a word that has already been used in the column & is offering it as a suggestion. This has nothing to do with the Data Validation, it occurs automatically in any Column. I think it is a default setting but it can be switched on or off from Tools -> Options & select Enable Autocomplete for cell values.

What you want would be available with a ComboBox but I wouldn't use that many Comboboxes in a sheet

Thank you. Any ideas on how to do the military time calculation in the second part of my question?

[JBeaucaire]

Put this formula in W2 and copy down:

=(TIME(LEFT(V2,2), RIGHT(V2,2), 0)) - (TIME(LEFT(T2,2), RIGHT(T2,2), 0)) + (1*(V2<T2))

Format those cells for h:mm.

There's an error in the data for V40. It's still getting the right answer but that's accidental. Make sure all the cells have 4-digit entries to avoid an actual miscalculation.

[davesexcel]

Is it the
Left,Right,Left,Right

that makes it military?

[DrEd]

Is it the
Left,Right,Left,Right

that makes it military?

I formatted cells T, V & W with custom h:mm, and added your formula to W as Validation/custom/ =(TIME(LEFT(V1,2), RIGHT(V1,2), 0)) - (TIME(LEFT(T1,2), RIGHT(T1,2), 0)) + (1*(V1<T1)) but it does not auto-calculate for cell W. I also must use : in the military time or it changes times to 0:00

Example: T=19:00, V= 21:30, so W should be auto-calculated as 2:30 but nothing happens in W. It stays blank.

Also, and exmapkle crossing midnight should be 2300-0200 = 3:00

[DrEd]

I formatted cells T, V & W with custom h:mm, and added your formula to W as Validation/custom/ =(TIME(LEFT(V1,2), RIGHT(V1,2), 0)) - (TIME(LEFT(T1,2), RIGHT(T1,2), 0)) + (1*(V1<T1)) but it does not auto-calculate for cell W. I also must use : in the military time or it changes times to 0:00

Example: T=19:00, V= 21:30, so W should be auto-calculated as 2:30 but nothing happens in W. It stays blank.

Also, and example crossing midnight should be 2300-0200 = 3:00

Military time begins at midnight 0000, each minute adds 1 such as 0001 is 1 minute after midnight. There is no a.m. or p.m. Noon is 1200, 1pm is 1300, 11:59 pm is 2359 then starts with 0000 next day. Not sure what you mean by 'left, right.'

[JBeaucaire]

This is all I meant, I put that formula in W2 and formatted properly, copied down.

[DrEd]

This is all I meant, I put that formula in W2 and formatted properly, copied down.

You are a genius. Thanks!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.