How to type in 2 decimal places in 1/4 hour display

Hello all.
MS EXCEL 2000

I have to change a time sheet that was previously in HH:mm to display in decimals, but to the nearest 1/4 hour ( 0.25 (=15 mins), 0.50 (= 30 mins), 0.75 (=45 mins), and 1.00 (1 hour))

This is not a formula question, but a format/display question as the cells have to be typed in by different people who up until now have beeb used to typing in the HH:mm format.

This means;
8:15hrs would become 8.25
8:30hrs would become 8.5
8:45hrs would become 8.75

The reason behind the change is a head office wallah that needs to have time in decimals and not true minutes.

Is there a way to;
prevent user from entering any decimal other than .00; .25;.50;.75
or change formatting to turn red if any other than above is entered.

Alternatively, is there a way of me (the collator for 50 people) being able to take data from their sheets (which I have to do monthly) which could be a formula...

I have tried to get head office to change, but they say that they collate data for 13 other sites and all the others are OK (thats cos all the others receive their data on PAPER and one person tabluates them)...

Any suggestions...

I dont know if I have explained myself properly, the people filling the new sheets in, dont need to put the : to make the cell time (they used to on previous versions as we had the output in HH:mm.

As of April a new sheet will be available (if I get my finger out), they can type any decimal they want, but it must be rounded to the amounts above, If you take away the fact that its a timesheet, and think of it as pure data entry, it might make it simpler to see what I am looking for.

Thanks for the speedy reply BTW

If they can then enter, say 8.35, you could use =ROUND(A1*4,0)/4 to get to the quarter hour.

Originally Posted by darkyam

If they can then enter, say 8.35, you could use =ROUND(A1*4,0)/4 to get to the quarter hour.

That works for me.

Obviously I cannot have this forumula in the same cell, to I assume, I should have 'helper' columns with the formulae in, and then reference these on my master sheet which shows all detail for the 50 or so staff.... Or would it be better to have the forulae run on my single sheet (which I have control over and does not need to look so smart) as opposed to the ones they fill in.

I will have a play.

Thanks for your help

Also, if the people who fill the sheets in, accidentally enter a : as they have done for the last two years, Excel will treat the result as a date/time, and wont work for the rest of my formulae.

Ive looked at conditional formatting to try and prevent this (ie if a : is enterered) but not been successful.

Could I format the cells as decimals and somehow lock them from having their format changed? or have the cell turn red if a time is entered?

I don't think data validation will work, but you can do conditional formatting to turn cells red with =MOD(A1,1/96)>0.
As shg said, you can't have formatting change the underlying number; however, with a helper column, you can change time whether entered as decimal or h:mm with this formula: =IF(A1>1,ROUND(A1*4,0)/4,ROUND(A1*96,0)/4). Note that this will give you the hours as numbers greater than 1, so if you need to convert back to hours for showing in Excel, you will have to divide the results by 24. HTH.

Edit: I guess I'm not quite as quick as shg.