Software

Ask something
Discussion Locked

Excel and Time Values

0 helpful
7 replies

by rawle

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to format time values that are in ddd:hh:mm to just minutes. For example, I would want to convert 000:02:17 to 137 minutes. But instead I get Sat:00:02:17 using the following formula =TIMEVALUE(RIGHT(A2,8)) and it gets even worse when itgoes to 23:21:14! It comes out Sat:23:21:14 which should would be 34,380 minutes. The reason for this is am trying gather stats such as median, stdev, average, etc. Is there a way to format these cells to just minutes?

I tried separating 000:00:00 to 000 00 00. Putting it into Access to separate to different column and then bring it back to Excel to multiply days by 1440 and hours by 60 and then adding minutes back into the other two fields....I am sure there was a shorter way!

7 replies

by xboss

Try this one.

1. Fix a cell in your sheet with a value of 00:00:00.
2. Instead of bringing 8 characters over, bring only five. =TIMEVALUE(RIGHT(A2,5)) will give you only your hours and minutes.
3. Deduct the value of your fixed cell from this and multiply by 1440. =(TIMEVALUE(RIGHT(A8,5))-A1)*1440). This will give you the number of minutes from the fixed cell.
4.Format the cell as a general value.
5.Adjust the value of the fixed cell depending on where your starting point is.

Hope this works for you.

br
Mike

by aisling

If it's just a simple formula you want and the time 02:17 is in A1 in the right format of hh:mm try this

=HOUR(A2)*60 + MINUTE(A2)

This discussion is closed to new replies.