# Why is there a date in my Excel time format, and how do I get rid of it?

I am using Excel at work and I have a list of users' online times.

I get a total say `180:30:00`, which is 180hrs 30 mins and 0 seconds.

I need to keep this cell as the time, but I want to work out how many sales per hour they have made, so I need another cell linking to the total cell. But I need the format to be in `180.5`.

The format I use for the `180:30:00` cell and all my times is `[H]:mm:ss`. To get the time into decimal I was going to use the left middle and right formulas to break the time into 3 different cells and then do the `hour + minute/60` formula, but I have came across an issue.

When I click on the cell `180:30:00` on the top line where you can enter formulae I have `07/01/2001 180:30:00`. So when I apply the left middle and right formula it picks up the date as well.

Can you please explain to me how I could change the format of the cell to lose the `07/01/2001` – or is it always there when you involve time at all?

I'm not at work so I can't post a screenshot right now.

-

I would assume that the date/time values in your spreadsheet have been entered as strings; otherwise, Excel would have changed `07/01/2001 180:30:00` to `07/08/2001 12:30:00` in the Formula bar. Excel carries over hours in excess of 24, converts them to days and adds them to the date.

Use the formula that you have to extract the time segment of the date/time string. I believe you have something similar to this:

``````=RIGHT(A1,FIND(" ",A1,1)-2)
``````

Multiply the result by 24, then set the cell format to General to get `180.5`.

Generally, though, you can extract the time from date/time values using this formula:

``````=A1-INT(A1)
``````

If A1 contains `1/1/1900 12:30:00`, this formula returns 12:30:00. Make sure the cell uses the number format `[h]:mm:ss`.

To get hour fractions instead, change the formula to:

``````=24*(A1-INT(A1))
``````

From which you'll get 12.5.

-
Thank you kaze makes sense and i will give it a go monday morning – user68062 Aug 19 '11 at 21:49

Excel stores dates and times as a number representing the number of days since epoch (1900-Jan-0), plus a fractional portion of a 24 hour day. The date portion will always be there.