Convert Decimal hours and Minutes to minutes please.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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" <[email protected]> wrote in message
> > >> >> news:[email protected]...
> > >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
>
>
> =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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >> > 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" <[email protected]> wrote in message
>> > >> >> news:[email protected]...
>> > >> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > =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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > 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" <[email protected]> wrote in message
> >> > >> news:[email protected]...
> >> > >> > 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" <[email protected]> wrote in message
> >> > >> >> news:[email protected]...
> >> > >> >> > 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+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

"Steved" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> >
>> > =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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > 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" <[email protected]> wrote in message
>> >> > >> news:[email protected]...
>> >> > >> > 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" <[email protected]> wrote in message
>> >> > >> >> news:[email protected]...
>> >> > >> >> > 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+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
>
> "Steved" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> >
> >> > =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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> > > 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" <[email protected]> wrote in message
> >> >> > >> news:[email protected]...
> >> >> > >> > 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" <[email protected]> wrote in message
> >> >> > >> >> news:[email protected]...
> >> >> > >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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+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
>>
>> "Steved" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >
>> >> >
>> >> > =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" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> > > 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" <[email protected]> wrote in message
>> >> >> > >> news:[email protected]...
>> >> >> > >> > 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" <[email protected]> wrote in
>> >> >> > >> >> message
>> >> >> > >> >> news:[email protected]...
>> >> >> > >> >> > Hello from Steved
>> >> >> > >> >> >
>> >> >> > >> >> > What formula will convert 4.50 to 530 minutes ( "Decimal
>> >> >> > >> >> > Time" )
>> >> >> > >> >> >
>> >> >> > >> >> > another example 16.50 to 1250 minutes.
>> >> >> > >> >> >
>> >> >> > >> >> > Thankyou.
>> >> >> > >> >>
>> >> >> > >> >>
>> >> >> > >> >>
>> >> >> > >>
>> >> >> > >>
>> >> >> > >>
>> >> >> >
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>
>>
>>