times seperator as a "." instead of ":" plus validation

[Sirishgreen]

hi i have a problem.

firstly i would like to validate some cells so that all the user can enter is times form a 24 hour clock.

Then i would like it to accept times with the seperator as a "." instead of ":" thanks in advance.

Simon Green

[NBVC]

so basically you want to be able to enter decimal values between 0 and 24?

Try picking Decimal from Validation with min=0 and max=24...

you will need to convert these to real times within formulas that refer to these numbers....

[Sirishgreen]

I am creating a payroll spreadsheet.
I have a piece of code which allows me to let people enter times as "600" and "1800". which is convienient becuase it saves putting in the ":". But i also do not want people to be able to put entries in which are backwards in time but after i have added the code, the validation i have put on the cells doesnt work anymore. any ideas?

Please Login or Register  to view this content.

[BigBas]

What do you mean by "backwards in time"? What are you trying to prevent?

[Sirishgreen]

I dont want them to be able to enter times that dont make sense

There is a morning and a afternoon shift. people can work both.

so they cant start in the afternoon before they finished in the morning. i.e they finished morning shift at 14:00pm they cant start afternoon at 13:00pm

I have the cells already validated they way i want them but its as if the code in previous post cancels it out.

[Sirishgreen]

I would like my items in a validation list to appear as the colours I have used in the original list for my dropdown.

[oldchippy]

Take a look here, does this help?

http://www.excelforum.com/showthread...ht=conditional

[Sirishgreen]

that did the trick thank you lucky i only had 4 entries though becuase only lets you put 3 conditions on there, so i used black for one of them.

Thanks again.

[Sirishgreen]

ok I need some serious help,

I am designing a TIMESHEET style spreadsheet. I want to restrict the entries that the users can make to reduce errors and also use some kind of input mask.

In terms of the validating it goes like this:

There are 6 points of data entry on each day for the user.

Morning (in - out) break

Evening (in-out) break

The "morning in" can be anytime, but the "morning out" has to be a time after "morning in"

Then the "afternoon in" has to be a time after "morning out"

and finally "afternoon out" has to be after "afternoon in" and before "morning in" (so they cant work a 24 hour shift)

no validation for breaks required.

They above works fine, and i can manage that by myself just using simple time validation. However, I also need to stop text from being entered in the cells, and add the input mask code (so i dont need to use the colon). 17:00 = 1700. when i tried it the input mask code overrides all the validation, so none of it works. I need all of the above to work together.

Thanks in advance

sirishgreen.