data entry type number (1400) formatted as military 14:00

[Louise99]

In other words, they don't want to type in the colon every time. If they type, for instance, 1555, I would like the cell to display 15:55 and be able to use this as a real time (in order to use it in formulas that find time differences).

Is this possible?

Thanks!

 

[SkipVought]

hi,

No!

You need to read & understand faq68-5827.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arthurbr]

Chip Pearson has a possible solution for you at

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[SkipVought]

arthurbr, well SURE, you can write a program to help lazy data entry. It's better to use the tool correctly, than to crutch them up, IMHO.

Now, they are going to have to enable macros each time the workbook loads. I can hear the moaning now!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Markus4]

Hi,

how about using a "helper" column? Data is entered as a three- or four-digit number in A1, the neighbouring cell makes the time:

=IF(OR(A1>2399;A1<1);"No valid time!";LEFT(A1;IF(A1>999;2;1))/24+RIGHT(A1;2)/24/60)

I know this is not really elegant, but I think it's viable.

HTH.

Markus
___________________________
I do not know whether in the formulae the ; has to be replaced by ,

 

[Gruuuu]

Markus, decent enough idea, but will cause problems. The biggest problem: you will NEVER HAVE TIME VALUES ON THE SHEET. No way to calculate time variances. Skip is right, The end user is best served by sucking it up and entering actual data. Data scrubbing is dirty work. Louise, your toughest task is in convincing them that this is a bad idea. If it helps, relay to them that pros in the field are undecided as to whether this is a bad idea or a terrible idea. Humor often helps get people on your side.

If they absolutely ABSOLUTELY will not budge,

=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

But this in a column, change the reference to the appropriate cell. Use that column to calculate from, and make sure that the formula is far enough down in the column to catch all the horribly entered data.

 

[xlhelp]

Or, how about just simple
=--(TEXT(A1,"00\:00"))
and format the cell for h:mm

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[Gruuuu]

Even better

 

[Markus4]

Hi,

I hit the [Submit]-Button to fast to notice the faults of my solution. As I have no Internet at home and there was no possibility until now my answer is quite late.

I still think it might work with the following formula:

IF(OR(A1<1;A1>2399;VALUE(RIGHT(A1;2))>59);"No valid time;VALUE(RIGHT(A1;2))/24/60+IF(A1>59;VALUE(LEFT(A1;IF(A1>999;2;1)))/24;0))

To me it seems quite simple: User enters a four-digit number in one cell. The neighbouring cell validates and converts this.

A four-digit number can be converted into a valid time by using string and converting functions unsing this model:

- A number is entered in a cell. In the neighbouring cell this returns a string with at least one (>0) and at most four (>2399) characters.

- Of this string the last two characters show the minutes, the leading characters three and four show the hours (if present).

- As the minutes can not be greater than 59, an accordant error-check has to be added before converting the number entered to a string.

HTH

Markus