convert H:MM to Minutes?

[afs24]

How do I convert a H:MM format I.E. 4:30 into minutes? When I use the formula =convert(cell,"hr","mn") it gives me 11.25? I don't think it allows the colon? Any ideas how I can convert this? Thank you

 

[Oaktree]

Excel stores time as fractions of a day, so 4:30 is stored as 0.19 days.

=CONVERT(A1,"hr","mn") converts 0.19 hrs to minutes, giving you 11.25.

You'd first have to convert 0.19 days to the correct number of hours.

Try:

=CONVERT(A1*24,"hr","mn")

or just =A1*24*60, formatted as a number.

 

[JazzSP8]

Sorry to be a thread intruder, but I would just like to say "Thanks" for the complete randomness of "CONVERT"

I didn’t know that function existed and I think will come in VERY handy on a new project I’m working on that was having me thinking about ways to convert some data, problem solved before it existed I guess!

 

[barry houdini]

although be aware that CONVERT is an Analysis ToolPak add-in function.

For the above I prefer

=A1*1440

but you could also use

=CONVERT(A1,"day","mn")

or even

=A1 and format as [m]

Note: if you use the last of these the underlying cell value will be different to that generated by the first 2

 

[Oaktree]

Jazz, it's probably a good idea to double check at least a couple of the conversions you're doing. Some versions of Excel have been known to convert some things incorrectly:

http://support.microsoft.com/kb/283695/en-us

 

[JazzSP8]

And, thanks for the heads up Oaktree - I shall be careful