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

Question

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.}

Answer 1

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.

Answer 2

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.

see this page for more information on date and time calculation in excel