General discussion

Locked

Excel and Time Values

By rawle ·
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!

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel and Time Values

by DKlippert In reply to Excel and Time Values

See:
www.cpearson.com/excel/datetime.htm#TOC

One way to do it is to set up your data as text. (Put a ' in front of the numbers:
'23:21:14 or format as text before entry.
then use the formula:
=LEFT(A1,2)*1440+MID(A1,4,2)*60+RIGHT(A1,2)*1

Collapse -

Excel and Time Values

by rawle In reply to Excel and Time Values

Poster rated this answer

Collapse -

Excel and Time Values

by XBoss In reply to Excel and Time Values

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

Collapse -

Excel and Time Values

by rawle In reply to Excel and Time Values

Poster rated this answer

Collapse -

Excel and Time Values

by aisling In reply to Excel and Time Values

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)

Collapse -

Excel and Time Values

by rawle In reply to Excel and Time Values

Poster rated this answer

Collapse -

Excel and Time Values

by rawle In reply to Excel and Time Values

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums