[wirdip]

Hi,
I enter huge amounts of data into my spreadsheet (excel 2003) most of the data are times (24 hour clock) which have to be in the following format 12:00. it would speed up my input if I could omit the colon and have excel put it in for me. I have tried all the formatting options and none seem to work, any help would be appreciated.
Also some of the cells turn red if a delivery is more than 60 mins late, I need a simple way of counting these late arrivals. I thought of using countif function.
Regards Alan

[Plantsman]

Click on the column that will contain the times e.g. if you are going to enter the times into column F, then click on column F at the top of the spreadsheet. You'll see the entire column become highlighted.
Now select "Format" from Excel's top line menu and select "Cells..." from the list that appears.

The "Format Cells" window will appear on your screen. Make sure the "Number" tab is selected and click "Custom" under the "Category" header.

In the single line entry box under the word "Type"...overtype whatever is in the box (probably the word "General") with: 0":"00

That is: zero Quote colon quote zero zero - with no embedded spaces. This format tells Excel the take the numbers you enter into that cell, treat them as numbers, and insert a colon before the last two digits.

Click "OK" to dismiss the "Format Cells" window.
Now any set of numbers you enter into the column you formatted will be treated as a number not a time. In addition, you can now save a bit of time entering data because you don't have to enter the colon. For example: to enter a time of 21:45, simply enter 2145. When you press Enter or move to another cell, Excel will insert the colon for you.

[zabusant]

Hi wirdip!

There is no easy way to "work with colors" in excel. You would have to write some VBA code to deal with the problem, which is not really worth it in your case. Since the cells get colored on a condition (when a delivery is 60 minutes late), it is much easier to count that.

So, just insert a =NOW() function in an empty cell in an empty column (don't worry about the date that is also shown, excel is smart enough to deal with it). Then, in another column, substract the two times (lets say you put =NOW() in G1 and the original times are in column A, just select the next free column - H - and write in =$G$1-A1 and copy it downwards. Then you can use the simple countif function and of course you can hide these columns so they don't bother you.

I hope this helps.

[Aj_old]

I think you should use 2 columns in first (let say A) you input the time as you wanna and in the second you type this formula that will convert number you typed into time:

=TIME(LEFT(A2;LEN(A2)-2);(RIGHT(A2;2));0)

in this case in column B you'll have it not only shown as time but also stored as time!only that it will be from 0:00 to 23:59

About counting the number of "delivery is more than 60 mins late" you should use sumproduct function, instead of countif, for this you need in one cell to have current time (depend on the way you chose to store data as number or as time it will differ).

take a look at file I uploaded, in it are 2 option to do what you need!

[wirdip]

Thanks for all the help.

[Aj_old]

Originally Posted by wirdip

Thanks for all the help.

This mean that the problem is solved?
If so please mark the thread as solved!