Converting hours to minutes

[TC123]

Hello,

In column A1 I have 09:00 and in column B1 I have 12:30. I need a formula that will work out the time and then convert that into minutes. I have worked out that =B1-A1 works out the time which is 3.30 hours but I want this displayed as minutes. For example, this works out to be 210 minutes. Can anyone help? Thanks

 

[GlennUK]

If you just want it displayed as minutes use a custom number format of [mm]

 

[JamesW]

Hi,

Try:

=(B1-A1)*24*60

 

[Johnny C]

minutes = int(time2 - time 1) * 24 * 60
hours = int(minutes/60)
mins in hour = minutes mod 60
(you may need to try minutes + 1 mod 61 - 1 or similar, but I think it will work)

 

[T. Valko]

Hello,

In column A1 I have 09:00 and in column B1 I have 12:30. I need a formula that will work out the time and then convert that into minutes. I have worked out that =B1-A1 works out the time which is 3.30 hours but I want this displayed as minutes. For example, this works out to be 210 minutes. Can anyone help? Thanks

Try this...

=(B1-A1)*1440

1440 = number of minutes in a day (24hrs * 60min/hr = 1440)

 

[TC123]

Thank you so much, that's brilliant! Sorry to be a pain but I was also wondering if you could help me with one other small problem. I have 7 columns - A B C D E F G. In column G, I would like it to work out (in minutes) again what E2-A2 is (which is fine as I know how to do this now). However, I only want column G to work this out if columns B2 and D2 are empty. If they are not empty and have a figure I want column G to show 00:00. Does this make sense?

Thank you again

 

[Johnny C]

=if(and(B2=0, D2=0),E2-A2,0)
might need
=if(and(B2="", D2=""),E2-A2,0)
or even
=if(and(value(B2)=0, value(D2)=0),E2-A2,0)

 

[T. Valko]

Thank you so much, that's brilliant! Sorry to be a pain but I was also wondering if you could help me with one other small problem. I have 7 columns - A B C D E F G. In column G, I would like it to work out (in minutes) again what E2-A2 is (which is fine as I know how to do this now). However, I only want column G to work this out if columns B2 and D2 are empty. If they are not empty and have a figure I want column G to show 00:00. Does this make sense?

Thank you again

Try this...

=IF(COUNT(B2,D2)=2,0,IF(COUNT(A2,E2)=2,(E2-A2)*1440,0))