Excel.... date input mask

[Legends77]

I am currently using the following input mask in Access to allow users to input military time: 00:00;0;_

Is there an equal excel input mask for excel? ... I tried using this in excel but says it is not a good mask and I have been searching but no luck with any of the builtin's.

Any help is greatly appreciated.

 

[AnotherHiggins]

Excel does not have Input Masks.

What are you trying to accomplish?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Legends77]

Need to speed up data entry. Currently they have to enter "1" then ":" then "21" then "PM" for 1:21PM and "1",":","59","PM for 1:59PM. This is so the next column can automatically calculate total time of 38min. I was hoping to get it to where they could enter a string like 0121 and 0159 or any range like 1100 to 1300 and the time be calculated. This would speed up data entry significantly.

 

[SkipVought]

You need to understand this...

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Legends77]

What would be the suggested way to calculate a time then? We need to be able to enter a start "time" and end time or something of that fashion, quickly. Reason being, to avoid the users from having to spend time to enter each ":" and "am" or "pm" and have a cell auto calculate the duration. The rest of the form is enough data entry and time consuming. If the times were always on the qtr hour it would not be an issue. We could avoid the start and end and just have them enter duration directly, but since it could be 11:12 am to 1:53pm and there could be 30 or more items like this, the chance of them making duration errors is too great so we need the auto calculate. But at the same time, I would like to make the current date entry faster, hence the reason I am looking for an input mask or something like it.

 

[SkipVought]

The only way that I am aware of is to use VBA event code for column containing date/time values.

If you wish to pursue this option, please post your question in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW the keyboard shortcuts for CURRENT date & time are
[tt]
ctr+;
ctr+:
[/tt]
respectively.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

Uh,

1:21 PM is 13:21

1:59 PM is 13:59

If you enter those two times in two cells and have a third cell that is the different it will show 0:38.

Typing a colon is too much of a burden?

 

[SkipVought]

I really agree with mintj!!!

What's the big deal about entering...
[tt]
13:21
[/tt]
?????

FURTHERMORE...
[tt]
0121
[/tt]
could NEVER, NEVER, NEVER be converted to 1:21 PM, cuz, how would you EVER be able to enter 1:21 [red]AM[/red]???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Legends77]

Mintjulep,
Considering the other option, which is to type not only the colon but also "AM" or "PM", I would think it would not be too much of a burden and am going to go forward with that option.
However, unfortunately, those using the file are "hunt and peck" typers, therefore, I foresee continued "complaints". Therefore, I am still looking for other options and greatly appreciate any ideas.

Skip,
The current date and time will not work here becasue this is more of a run log and being filled in "after the fact".

 

[SkipVought]

I would like to make the current date entry faster,

Skip,
The current date and time will not work here becasue this is more of a run log and being filled in "after the fact".

Please be consistent, else you will loose credibility!

Also, you did not address your previous statement...

Need to speed up data entry. Currently they have to enter "1" then ":" then "21" then "PM" for 1:21PM and "1",":","59","PM for 1:59PM. This is so the next column can automatically calculate total time of 38min. I was hoping to get it to where they could enter a string like 0121 and 0159 or any range like 1100 to 1300 and the time be calculated. This would speed up data entry significantly.

where you intimate that entering 0121 could result in 1:21 PM, when this does not make sense!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You do not need to type AM/PM, depending on formatting, the formula bar and the cell can display different text.

If you just need to calculate elapsed time, enter values (not text!): 0159 and 0121 and calculate with:
=VALUE(SUBSTITUTE(TEXT(A1,"00x00"),"x",":"))-VALUE(SUBSTITUTE(TEXT(B1,"00x00"),"x",":"))
The price is that you loose time functionality of input cells.

combo

 

[Legends77]

Combo,
I have tried several ways with the formula you provided as it looks very promising but having problems. When I put the end "value" of 0159 in cell B1 and the beginning "value" of 0121, the formula returns:
-0.0264

 

[SkipVought]

Just calculate the ABSOLUTE difference...
[tt]
=[red]ABS([/red]VALUE(SUBSTITUTE(TEXT(A1,"00x00"),"x",":"))-VALUE(SUBSTITUTE(TEXT(B1,"00x00"),"x",":"))[red])[/red]

[/tt]
But tell me how you plan to calculate the difference between 11:55 AM and 1:05 PM using this approch.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

I would like to make the current date entry faster

Skip,

I believe he meant the current [blue]method in use[/blue] for date entry.

Randy

 

[SkipVought]

I know that that is what I meant NOW. It was not originally clear.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Format the result as time (should be positive, otherwise the cell will be filled with #'s).

IMHO the most promising way of writing time is input of real time with colon, as mintjulep pointed. You can put 13:00 and depending on formatting excel will display it whatever you like, but still keep proper value behind. You will be able to directly use it in future calculations.

combo