I'm having trouble formatting time with Excel can you help me?!?

Question

I've been having problems formatting time in excel so when I put in numbers in will automatically put the : in the time like 1200 entered will show up 12:00. But when I format under format cell, then time and then I click ok. But the numbers show up 0:00 no matter what time I put in. What am I doing wrong? even the regular time like 4:30 pm doesnt show up. military or regular HELP!!!!

Answer 1

Excel can not automatically convert 1200 to 12:00.
You need to use a macro in visual basic to do that.

You need to trap the worksheet change event to automatically do this for you. Look in the help section and work it out.

If you cant manage, contact me.

Answer 2

The conversion between number and time is not obvious. The whole part of the number is in days, and the value 2415 is today, 8/11/06, but all whole numbers correspond to the time 00:00 on whatever day. Times are actually determined by the decimal fraction, noon = .5. 9:00 AM = .25

Right now the time is 2415.98 = 11:31 PM on 8/11/06, at least where I am (CDT)

If you have a column of numbers without colons, you could convert to Excel time values. First convert your number to minutes, dividing your number by 100, multiplying that number by 60 and adding the remainder of the first division turns your number into minutes past midnight. Divide that by 1440.0 to get excel time values.

Answer 3

This is little tricky.

When you format a cell as 'Time', Excel assumes the number that is already entered in to the cell as "Date + Time". Not only as "Time". Decimal point is considered the manual separator for date and time.

So first format the cells in the desired time format.

Then enter the value in decimals. Following is the formula:
1second = 0.0000115740740740741

If you can't enter these values by yourself, just enter any positive value less than 1 (a decimal value) to the cell, then press the key and edit only the time fraction from there. That is much easier than entering manually or than writing lengthy formulas.

Answer 4

On this computer I have Excel97.

For a "time" column, I selected the column
Format
Cells
click Time (click on the format you want)
click o.k.

I selected that with the date and time
In a cell I entered today's date, 8/11/06 then hit "enter"
The date and time were entered

Hope this helps.

Answer 5

First select the cells you want to put times into then right click>format cells. Select the number tab and click time. Then select the time display that you want. Enter your times and it should work.

Answer 6

Maybe I can help. I need to see what format you are using.

[e-mail]

Answer 7

You have adjust the column size. Move your mouse pointer to the end of the column and click and drag it to where you can see the whole number.

Answer 8

To be honest, I don't know what's wrong with it. But I think that you should reinstall your excel.