Convert h:mm:ss to a number of minutes

[mildav]

Hello,

I'm having some trouble converting a time in the format h:mm:ss to a pure number of minutes.

Here is the scenario:
Cell A2 reads "12:07:00AM"

What I need it to read is just 7.00 or 7 without retaining the rest of the time format.

Any ideas on how to convert this? I've tried a variety of formulas everything is still returning the h:mm:ss coding behind the scenes.

Thanks!

David

[jeffreybrown]

Do you want this in the same cell?

What if you change the custom format to mm:ss

[mildav]

Adjacent cell would actually be optimal. The problem is it seems to retain the time format, which causes problems when I try and pivot the data to sum things up and do other analysis.The numbers on their own can be calculated, but the time format seems to really mess up the pivot tables, thus why I'd like to convert 12:07:00AM into a pure number.

[jeffreybrown]

Try...

=MINUTE(A2) >> returns 7

[mildav]

Hmmm...that worked for the individual issue, but I can't get it to work for the 1000 other rows of other times.

My current fix is pretty crude, but basically I took =A2*24*60/24 in an adjacent cell, converted this to a decimal, then in another cell multiplied by 24 to yield a pure number of 7.0000001 (close enough). Seemed to have done the trick for all the values. I highly doubt this way is optimal, but it did the trip (the multiplying and dividing by 24 seems redundant, but I couldn't get the right answer otherwise).

[jeffreybrown]

Without seeing your data it's hard to understand why the other rows did not work.

Do you have some sanitized sample data you can provide?

[mildav]

Sure, see sanitized data attached. I've included the 1000 or so rows I'm converting...

[jeffreybrown]

In your attachment I don't see where you tried...

=MINUTE(B3)

...but when I place this formula in B3 and copy down, I seem to get what you desire.

In B1046 which reads 2:50:00, =MINUTE(B1046) produces 50, and the row above 44...

Should you be getting something different?

[mildav]

I actually just gave it a try again with the formula you referenced, seems like it is working fine now as well. Very useful, seems like a simple way to convert! Thanks Jeffrey!

[jeffreybrown]

You're welcome…glad to know you have a workable solution now and thanks for the feedback...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[daddylonglegs]

If 2:50 should convert to 170 then just multiply by 1440 and format as number, e.g.

=B3*1440