Excel function for hh:mm

[lapot]

Dear all

Please see attached excel sheet. On Colum G (highlighted in green) there are hours and minutes. Is there a way I can create a function or macro so that it will convert the values to minutes. For example if the value 1:48 it will convert it 108 minutes or 0:36 will be 36 minutes.

I know I can use =Hour() or =MINUTES() formula but this is long way. My sheet has over 10k data.

kind regards

[Kaper]

Please Login or Register  to view this content.

and copy down

PS. of course you can write

Please Login or Register  to view this content.

but then you don't see why it works

other concept:
write in empty cell 1440
Ctrl+C
select all your green column,
Show Paste special dialog
check Multiple
Paste
(dont unselect) Format all cells in green column (still selected) as General

[lapot]

Thank you Kaper, it worked perfectly. you are a star

[:) Sixthsense :)]

In L2 Cell

=VALUE(TEXT(G2,"[m]"))

Drag it down...

[lapot]

Also is there a way getting a total at the bottom which will say so many hours and so many minutes. Say it is 1104 minutes when we convert that hours and minutes it should say 18 hours and 40 minutes.

[lapot]

I realised if there is -minus entry , it doesn't convert. for example -1:48 converts as #VALUE!, is there way to get round it?

[Kaper]

so once you have number of minutes in a range?
try:

Please Login or Register  to view this content.

[:) Sixthsense :)]

for example -1:48 converts as #VALUE!

=VALUE(TEXT(SUBSTITUTE(G2,"-",""),"[m]"))

[Kaper]

as for -
then it is probably cell with text not time (in excel perspective).

try

Please Login or Register  to view this content.

[lapot]

as for -
then it is probably cell with text not time (in excel perspective).

try

Please Login or Register  to view this content.

this has worked perfectly

[lapot]

so once you have number of minutes in a range?
try:

Please Login or Register  to view this content.

this has worked perfectly

many thanks

[lapot]

this has worked perfectly

many thanks