Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

Hello Bif

Firstly thankyou for the formula

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

Thankyou.


"Biff" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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

"Steved" wrote in message
...


=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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" wrote:

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

"Steved" wrote in message
...


=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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+MO D(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

"Steved" wrote in message
...
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" wrote:

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

"Steved" wrote in message
...


=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

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" wrote:

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+MO D(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

"Steved" wrote in message
...
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" wrote:

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

"Steved" wrote in message
...


=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.

Ok, if that's what you really wanted!

Thanks for the feedback and good luck!

Biff

"Steved" wrote in message
...
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" wrote:

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+MO D(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

"Steved" wrote in message
...
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" wrote:

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

"Steved" wrote in message
...


=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


"Steved" wrote:

Brilliant and I thankyou.

"Biff" wrote:

Try this:

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

Biff

"Steved" wrote in message
...
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" wrote:

16.50 is the same as 4:50 pm
another example 16.50 to 1250 minutes.

OK, but 16.50 still doesn't = 1250 minutes.

16*60+50 = 1010

Biff

"Steved" wrote in message
...
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.

"Chip Pearson" wrote:

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


"Steved" wrote in
message
...
Hello from Steved

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

another example 16.50 to 1250 minutes.

Thankyou.