Help with Data Input

[PaulBxx]

Hi All,
Excel Problem.JPG
I have attached a screen shot of the particular cells I want to ensure have the correct data entered.

Basically the D and E columns must be input as 00.00 not 00:00 or any other combination, is there a rule / command I can do to the individual cell(s) so that if someone inserts 13:45 instead of 13.45 it will come up with an error message and not allow the entry.

Thanks All

Great forum and help page by the way - i've learnt a lot this week already.

[Roel Jongman]

You can add Data validation on to those cells so you can limit input to be a time
That is assuming that your local settings of the workstation(s) are also set to show time wht the point and not the colon
you find cell validation in the Data menu

If you need more detailed help please attach an example file then it will be easier to show you how to make it work

[6StringJazzer]

Welcome to the Forum PaulBxx!

If you are requiring people to enter using a non-standard decimal format then data validation requiring a time will not work, because they not entering a time. From your description, you are not just displaying the time with a dot but people are entering it that way, so the underlying value is really 14.30, not the time value for 14:30.

First you should format the cells as Custom as "00.00". This will force leading and trailing zeroes in the display (not in how people type it in). Then you should use Data Validation to restrict the range of values from 1 to 24 (or whatever your valid time range is).

Then if a user types a time value, like 14:30, the underlying value is <1 and will be rejected by the data validation rule.

I have attached an example.

But I would like to discourage you from using that format. You lose all the Excel built-in support for managing time data. Your numbers are going to be very difficult to use in any time calculations, such as calculating the duration from start to finish. For example, 13.15 - 11.45 will give 1.70, not 1.30. What else are you doing with those numbers?

[PaulBxx]

Hi all

Thanks for the replies...

Jeff, what I am trying to do is allow a user to input a start time, finish time and then in the next box it auto gives the total hours and minutes that they have completed.

This then allows the next box which has a select function for the user of time(1), time 1/2 (1 1/2) and double time (2).

Once that has been selected the next box is the sum of hours and minutes done by the type of overtime completed.

I was having a problem as you quite rightly say it coverts to a decimal etc so I tried the 13.45 format and it kind of works!

Is there something I have missed to allow this?

Thanks

Paul

[6StringJazzer]

For example, 13.15 - 11.45 will give 1.70, not 1.30.

allow a user to input a start time, finish time and then in the next box it auto gives the total hours and minutes that they have completed.

How are you calculating the hours and minutes they have completed? As I described, the arithmetic doesn't work.

[PaulBxx]

Hi,

I’m just using the standard if

Start is 11.30 and finish 12.30 I just do a subtract function and it gives me 1 etc

Simple I know but I couldn’t get the time 11:30 12:30 to put the correct amount of 1 hour in the next box it put the decimal type in as you said

I just format the cells to general number and ensure as I mentioned in first post that the user inserts as 12.30 and not 12:30 otherwise it brings the wrong result back

Basically the theee columns need to be start time, end time and then total hours worked.

[6StringJazzer]

What you are doing is very common.

Please attach your file and I'll demonstrate a solution.

The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[PaulBxx]

Hi,

I’m off work until Tuesday night so will upload then for you to have a look at the result I am trying to achieve.

Thank you very much I appreciate your time. ��