Convert Decimal hours and Minutes to minutes please.

[Guest]

Hello from Steved

What formula will convert 4.50 to 530 minutes ( "Decimal Time" )

another example 16.50 to 1250 minutes.

Thankyou.

 

[Chip Pearson]

The short answer is multiply by 60. But your numbers are all
wrong. Assuming that 16.50 is 16 and 1/2 hours, the correct
number of minutes is 990, not 1250. Similarly 4.50 is 270
minutes, not 530. Am I missing something?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Hello Chip from Steved

I ran a payroll which was designed about 40 years ago so in my exercise
16.50 is the same as 4:50 pm. I am in the process off converting the payroll
to minutes.

 

[Biff]

16.50 is the same as 4:50 pm
OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

 

[Guest]

Hello Bif

Firstly thankyou for the formula

Secondly I caculate wrong asyou rightfully say it is1010 ( to much in a
Hurry )

Thankyou.

 

[Guest]

ps

ok if I have 4.50 in A1 and P in A2

P is afternoon
Is it possible for the formula to look at P in A2 and add 720 so the answer
is 1010 please.

Thankyou

 

[Biff]

Try this:

=IF(A1="",0,INT(A1+(A2="P")*12)*60+MOD(A1,1)*100)

Biff

 

[Guest]

Brilliant and I thankyou.

Try this:

=IF(A1="",0,INT(A1+(A2="P")*12)*60+MOD(A1,1)*100)

Biff

 

[Guest]

=IF(A1="",0,INT(A1+(A2="P")*12)*60+MOD(A1,1)*100)

1.00 P is giving me 60 What is required please to add 12 hours and make it 780

Thankyou

 

[Biff]

1.00 P is giving me 60 What is required please to add 12 hours and make it

780

I get 780. ???

However, I did run across a potential bug when dealing with 12 AM and 12 PM.

How would you enter 12:30 AM? And, am I to understand that if cell A2 does
not contain "P" then it assumed the time in A1 is AM?

Biff

 

[Guest]

Hello from Steved

Sorry a type error on my part 1.00 P is as you corrrectly pointed out 780.

I treat 12.00 A as 0 ( The Beginning off the day ) and 12.00 P as 720 ( The
Middle off the day ), I notice in your formula 12.00 P is 1440 and 12.00 A
720, you are so close is there away using your formula to make change so that
12.00 A reconizes it as 0 and 12.00 P as 720. Thanks so far for what you have
done.

 

[Biff]

Steve, first thing, this is not a very good way to keep track of time. It's
just full of potential bugs.

Try this:

=IF(A1="",0,IF(A1<1,A1*100,IF(A1>=12,INT(A1)*60+MOD(A1,1)*100,INT(A1+(A2="P")*12)*60+MOD(A1,1)*100)))

Things that I'm assuming since you didn't provide a thorough explanation of
what you want or what you're trying to do:

12:00 AM is entered as 0.00
12:01 AM to 12:59 AM is entered as a decimal value - 0.30 is 12:30 AM
1:00 AM to 11:59 AM is entered as a decimal value *AND* cell A2 is *EMPTY*:

A1 = 2.50
A2 = empty

So, A1 = 2:50 AM

12:00 PM to 11:59 PM is entered as a decimal value *AND* cell A2 *CONTAINS*
"P":

A1 = 10.20
A2 = P

So, A1 = 10:20 PM

The above formula will return a result in the range 0 to 1439. There are
1440 minutes in a day so 11:59 PM = 1439. After 11:59 PM the time rolls over
to 12:00 AM which starts a new day and thus has the value of 0.

I highly recommend that you NOT use this method of timekeeping.

Biff

 

[Guest]

Hello Biff

Biff all I am doing is converting the time to minutes for a new payroll for
about 850 people. What we are doing isistead off the normal 8:00 am Start and
4:30 pm finishes we will use 480 and 990. I am using your formula to convert,
which I say thankyou for your time. Yes I've taken on board what you have
written, but I am very pleased as to what you have done for me, and from this
point I can finish this project.

Thankyou.

 

[Biff]

Ok, if that's what you really wanted!

Thanks for the feedback and good luck!

Biff